#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>
#include <string.h>
#include <time.h>

#if defined POSIX || (defined __APPLE__ && defined __MACH__)
	#define CLEARSCR system("clear")
#elif defined MSDOS || defined WIN32
	#define CLEARSCR system("cls")
#endif

#if defined POSIX || (defined __APPLE__ && defined __MACH__)
#include <termios.h>

static struct termios old, new;

/* Initialize new terminal i/o settings */
void initTermios(int echo)
{
    tcgetattr(0, &old); /* grab old terminal i/o settings */
    new = old; /* make new settings same as old settings */
    new.c_lflag &= ~ICANON; /* disable buffered i/o */
    new.c_lflag &= echo ? ECHO : ~ECHO; /* set echo mode */
    tcsetattr(0, TCSANOW, &new); /* use these new terminal i/o settings now */
}

/* Restore old terminal i/o settings */
void resetTermios(void)
{
    tcsetattr(0, TCSANOW, &old);
}

/* Read 1 character - echo defines echo mode */
char getch_(int echo)
{
    char ch;
    initTermios(echo);
    ch = getchar();
    resetTermios();
    return ch;
}

/* Read 1 character without echo */
char getch(void)
{
    return getch_(0);
}

/* Read 1 character with echo */
char getche(void)
{
    return getch_(1);
}
#endif

#define ENTER 13
#define LINE_FEED 10
#define BACKSPACE 8
#define DELETE 127

int utilIsBackspace(char character) {
#if defined __APPLE__ && __MACH__
    if (character == DELETE) {
        return 1;
    }
#endif
    if (character == BACKSPACE) {
        return 1;
    }
    else {
        return 0;
    }
}

int utilIsEnter(char character) {
#if defined __APPLE__ && __MACH__
    if (character == LINE_FEED) {
        return 1;
    }
#endif
    if (character == ENTER) {
        return 1;
    }
    else {
        return 0;
    }
}

int utilFlush() {
#if (defined __APPLE__ && defined __MACH__) || defined POSIX
    return fpurge(stdin);
#elif defined MSDOS || defined WIN32
    return fflush(stdin);
#endif
}

typedef char string1000[1001];
typedef char string800[801];
typedef char string500[501];
typedef char string300[301];
typedef char string150[151];
typedef char string100[101];
typedef char string50 [51];
typedef char string40[41];
typedef char string30[31];
typedef char string20[21];
typedef char string3[4];

/**TABLE SPECIFICATIONS**/

struct sectionTable {
	char* tableName;
	char* sectionId;
	char* sectionName;
	char* professorName;
};

const struct sectionTable SectionTable = {
	"section",
	"section_id",
	"section_name",
	"professor_name"
};

struct userTable {
	char* tableName;
	char* userId;
	char* userName;
	char* password;
	char* sectionId;
};

const struct userTable UserTable = {
	"user",
	"user_id",
	"user_name",
	"password",
	"section_id"
};

struct phaseTable {
	char* tableName;
	char* phaseId;
	char* phaseName;
};

const struct phaseTable PhaseTable = {
	"phase", "phase_id", "phase_name"
};

struct taskPlanTable {
	char* tableName;
	char* taskPlanId;
	char* userProjectId;
};

const struct taskPlanTable TaskPlanTable = {"taskplan", "taskplan_id", "user_project_id"};

struct taskPlanEntryTable {
	char* tableName;
	char* entryId;
	char* taskPlanId;
	char* phaseId;
	char* datePlanned;
	char* dateAccomplished;
};

const struct taskPlanEntryTable TaskPlanEntryTable = {"taskplanentry", "entry_id", "taskplan_id", "phase_id", "date_planned", "date_accomplished"};


struct recordTable {
	char* tableName;
	char* logId;
	char* userProjectId;
	char* phaseId;
	char* startTime;
	char* endTime;
	char* remarks;
};

const struct recordTable RecordTable = {
	"timelog",
	"log_id",
	"user_project_id",
	"phase_id",
	"start_time",
	"end_time",
	"remarks"
};

struct interruptionTable {
	char* tableName;
	char* interruptionId;
	char* logId;
	char* startTime;
	char* endTime;
	char* reason;
};

const struct interruptionTable InterruptionTable = {
	"interruption",
	"interruption_id",
	"log_id",
	"start_time",
	"end_time",
	"reason"
};

struct defectTypeTable {
	char* tableName;
	char* typeId;
	char* typeName;
	char* typeDesc;
};

const struct defectTypeTable DefectTypeTable = { "defecttype", "type_id", "type_name", "type_desc" };

struct projectTable {
	char* userProjectId;
	char* userId;
	char* pspId;
	char* currentPhase;
	char* done;
};

const struct projectTable ProjectTable = {
	"user_project_id",
	"user_id",
	"psp_id",
	"current_phase",
	"done"
};

struct defectTable {
	char* tableName;
	char* defectId;
	char* userProjectId;
	char* injectPhase;
	char* fixPhase;
	char* startFixTime;
	char* endFixTime;
	char* description;
	char* typeId;
};

const struct defectTable DefectTable = {"defect", "defect_id", "user_project_id", "inject_phase", "fix_phase", "start_fix_time", "end_fix_time", "description", "type_id"};

struct fixPauseTable {
	char* tableName;
	char* pauseId;
	char* defectId;
	char* startTime;
	char* endTime;
};

const struct fixPauseTable FixPauseTable = {"fixpause", "pause_id", "defect_id", "start_time", "end_time"};

struct defectDependencyTable {
	char* tableName;
	char* dependencyId;
	char* defectCreatedId;
	char* defectOriginalId;
};

const struct defectDependencyTable DefectDependencyTable = {"defectdependency", "dependency_id", "defect_created_id", "defect_original_id"};

struct trtTable {
	string20 tableName;
	string20 testNumber;
	string20 userProjectId;
	string20 testObjective;
	string20 testConditions;
	string20 expectedResults;
	string20 actualResults;
	string20 testDescription;
};

const struct trtTable TrtTable = {
	"testreport",
	"test_number",
	"user_project_id",
	"test_objective",
	"test_conditions",
	"expected_results",
	"actual_results",
	"test_description"
};

struct procImpPlanTable{
	string20 tableName;
	string20 piplanId;
	string20 userProjectId;
	string20 notes;
};

const struct procImpPlanTable ProcImpPlanTable = {
	"piplan",
	"piplan_id",
	"user_project_id",
	"notes"
};

struct procImpProbTable{
	string20 tableName;
	string20 problemId;
	string20 piplanId;
	string20 description;
};

const struct procImpProbTable ProcImpProbTable = {
	"pipproblem",
	"problem_id",
	"piplan_id",
	"description"
};

struct procImpPropTable{
	string20 tableName;
	string20 proposalId;
	string20 piplanId;
	string20 description;
};

const struct procImpPropTable ProcImpPropTable = {
	"pipproposal",
	"proposal_id",
	"piplan_id",
	"description"
};

struct planSummaryTable {
    char* tableName;
    char* planId;
    char* userProjectId;
};

const struct planSummaryTable PlanSummaryTable = {"plansummary", "plan_id", "user_project_id"};

/**DATA TYPES**/
typedef struct
{
    string40 usmUserName;
    string20 usmPassword;
    string3 usmSection;
} UserAccount;

typedef struct {
	int userId;
	char* userName;
	char* password;
	int sectionId;
} User;

typedef struct {
	int phaseId;
	char* phaseName;
} Phase;

typedef struct {
	int taskPlanId;
	int userProjectId;
} TaskPlan;

typedef struct {
	int entryId;
	int taskPlanId;
	int phaseId;
	time_t datePlanned;
	time_t dateAccomplished;
} TaskPlanEntry;

typedef struct {
	int logId;
	int userProjectId;
	int phaseId;
	time_t startTime;
	time_t endTime;
	char* reason;
} Record;

typedef struct {
	int interruptionId;
	int logId;
	time_t startTime;
	time_t endTime;
	char* reason;
} Interruption;

typedef struct {
	int userProjectId;
	int userId;
	int pspId;
	int currentPhase;
	int done;
} Project;

typedef struct {
	int typeId;
	char* typeName;
	char* typeDesc;
} DefectType;

typedef struct {
	int defectId;
	int userProjectId;
	int injectPhase;
	int fixPhase;
	time_t startFixTime;
	time_t endFixTime;
	char* description;
	int typeId;
} Defect;

typedef struct {
	int pauseId;
	int defectId;
	long startTime;
	long endTime;
} FixPause;

typedef struct {
	int dependencyId;
	int defectCreatedId;
	int defectOriginalId;
} DefectDependency;

typedef struct {
	int number;
	char* name;
	float plannedHours;
	float plannedValue;
	float cumulativeHours;
	float cumulativePlannedValue;
	char* plannedDate;
	char* actualDate;
	float actualEarnedValue;
	float actualCumulativeValue;
} TaskPlanDisplay;

typedef struct{
	int testNumber;
	int userProjectId;
	string500 testObjective;
	string500 testConditions;
	string500 expectedResults;
	string500 actualResults;
	string500 testDescription;
} TestReport;

typedef struct{
	int piplanId;
	int userProjectId;
	string800 notes;
} ProcImpPlan;

typedef struct{
	int problemId;
	int piplanId;
	string300 description;
} ProcImpProb;

typedef struct{
	int proposalId;
	int piplanId;
	string300 description;
} ProcImpProp;

typedef struct
{
    string3 usmSectionName;
    string40 usmProfessor;
} Section;

typedef struct {
    int plannedTimeId;
	int planId;
	int planningTime;
	int designTime;
	int codeTime;
	int compileTime;
	int testTime;
	int postMortemTime;
	int totalTime;
} PlannedTime;

typedef struct {
    int planInfoId;
	int planId;
	int planLOCPerHour;
	int planTotalTime;
	float planPercentReused;
	float planPercentNewReused;
	float actualPercentReused;
	float actualPercentNewReused;
} PlanInfo;

typedef struct {
    int planId;
    int userProjectId;
} PlanSummary;

typedef struct {
    int plannedLOCId;
    int planId;
    float planBaseLOC;
    float planDeletedLOC;
    float planModifiedLOC;
    float planAddedLOC;
    float planReusedLOC;
    float planTotalNewAndChanged;
} PlannedLOC;

typedef struct {
    int actualLOCId;
    int planId;
    int actualBaseLOC;
    int actualDeletedLOC;
    int actualModifiedLOC;
    int actualAddedLOC;
    int actualReusedLOC;
    int actualTotalNewAndChanged;
} ActualLOC;

typedef struct {
    int planningInjected;
    int designInjected;
    int codeInjected;
    int compileInjected;
    int testInjected;
    int totalDevelopmentInjected;
} DefectsInjected;

typedef struct {
    int planningRemoved;
    int designRemoved;
    int codeRemoved;
    int compileRemoved;
    int testRemoved;
    int totalDevelopmentRemoved;
    int afterDevelopmentInjected;
} DefectsRemoved;

typedef struct {
    int template_id;
    int user_project_id;
} SizeEstimate;

typedef struct {
    int newobject_id;
    int template_id;
    char function_name[100];
    int objectsize_id;
} NewObject;

typedef struct {
    int baseAddition_id;
    int template_id;
    char function_name[100];
    int objectsize_id;
} BaseAddition;

typedef struct {
    int reusedobject_id;
    int template_id;
    char function_name[100];
    float function_lines;
} ReusedObject;

typedef struct {
    int baseSize;
    int LOCDeleted;
    int LOCModified;
} BaseProgram;

typedef struct {
    int sizeId;
    char* sizeName;
} SizeType;

typedef struct {
    int typeId;
    char* typeName;
} ObjectType;

typedef struct {
    int objectSizeId;
    int typeId;
    int sizeId;
    float size;
} ObjectSize;

typedef struct {
    int number;
    char date[10];
    float planDirectHours;
    float planCumulativeHours;
    float planCumulativePlannedValue;
    float actualDirectHours;
    float actualCumulativeHours;
    float cumulativeEarnedValue;
    float adjustedEarnedValue;
} sptRow;

/**MISCELLANEOUS**/
const char* MONTHS[12] = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};

struct calendarDate {
	int month;
	int day;
	int year;
};

const int YEAR_STANDARD = 1900;

const char* DB_NAME = "group1902tsp";
const char* CREATE_SCRIPT_FILE = "scripts/create_schema.sql";
const char* PHASE_FILE = "scripts/1 - phase.sql";
const char* DEFECT_FILE = "scripts/2 - defectType.sql";
const char* SIZE_FILE = "scripts/3 - sizetype.sql";
const char* OBJECT_FILE = "scripts/4 - objecttype.sql";
const char* OBJECT_SIZE_FILE = "scripts/5 - objectsize.sql";
const char* SECTION_FILE = "scripts/6 - section.sql";

const int MAX_ARBITRARY_STRING_LENGTH = 9999;

/**FUNCTION PROTOTYPES**/
/**UTILITY FUNCTIONS**/
void utilEscapeStringValue (char** stringValue);
void utilFormatTime (const time_t time, char** timeString);
time_t utilGetCurrentCalendarDate (int* month, int* day, int* year);
void utilFormatDate (const time_t time, char** timeString);
void utilStringifyFile (FILE* filePtr, char** stringified);

/**DATABASE CONNECTION**/
void utilPrintError (MYSQL* conn, char* message);
MYSQL *dbConnectDatabase (char* server, char* username, char* password, char* dbName,
                          unsigned int portNumber, char* socketName, unsigned int flags);
void dbDisconnectDatabase (MYSQL* mySqlInstance);
MYSQL* dbConnectDefaultDatabase (char* username, char* password);
int dbRetrieveLogin(char** userName, char** password);
void dbStoreLogin(char* userName, char* password);
void dbAskLogin(char** userName, char** password);
int dbCheckDatabaseExists (MYSQL* conn, char* dbName);
int dbExecuteSchemaFiles (MYSQL* conn);

/**PHASE MODULE (PRI)**/
Phase priConvertRow (MYSQL_ROW row, unsigned int numberOfFields);
unsigned long priGetPhases (MYSQL* conn, Phase** phase);
unsigned long priExecuteQuery (MYSQL* conn, char* query, Phase** phases);
Phase* priGetPhaseById (MYSQL* conn, int phaseId);

/*TASK PLANNING TEMPLATE (TPT)*/
TaskPlan tptConvertTaskPlanRow (MYSQL_ROW row, unsigned int numberOfFields);
unsigned long tptExecuteTaskPlanQuery (MYSQL* conn, char* query, TaskPlan** plans);
unsigned long tptGetTaskPlanById(MYSQL* conn, unsigned long long id, TaskPlan** plans);
TaskPlan* tptCreateTaskPlan(MYSQL* conn, Project project);
TaskPlan* tptGetTaskPlanForProject (MYSQL* conn, Project project);
TaskPlanEntry tptConvertTaskPlanEntryRow(MYSQL_ROW row, unsigned int numberOfFields);
unsigned long tptExecuteTaskPlanEntryQuery(MYSQL* conn, char* query, TaskPlanEntry** entries);
unsigned long tptGetTaskPlanEntryById(MYSQL* conn, int id, TaskPlanEntry** entries);
void tptInsertTaskPlanEntry (MYSQL* conn, TaskPlanEntry entry);
unsigned long tptGetTaskPlanEntriesFromPlan (MYSQL* conn, TaskPlan plan, TaskPlanEntry** entries);
unsigned long tptGetTaskPlanEntriesFromProject (MYSQL* conn, Project project, TaskPlanEntry** entries);
TaskPlanEntry* tptGetProjectTaskPlanEntryWithPhase (MYSQL* conn, TaskPlan plan, Phase phase);
TaskPlanEntry* tptGetTaskPlanEntryWithProjectAndPhase (MYSQL* conn, Project project, Phase phase);
void tptUpdateTaskPlanEntry (MYSQL* conn, TaskPlanEntry entry);
void tptUpdateTaskPlanEntryOnPhaseDone (MYSQL* conn, Project project, Phase phase, time_t stopTime);
int tptCheckTaskPlanningTemplate(MYSQL* conn, Project project);

/**TIME RECORDING LOG (TRL)**/
Interruption trlConvertInterruptionRow(MYSQL_ROW row, unsigned int numberOfFields);
void trlInsertRecord (MYSQL* conn, Record timeLog);
void trlUpdateRecord (MYSQL* conn, Record record);
Record trlConvertRecordRow (MYSQL_ROW row, unsigned int numberOfFields);
unsigned long trlGetTimeLogsForProject (MYSQL* conn, Project project, Record** records);
Record* trlGetTimeLogForPhase (MYSQL* conn, Project project, Phase phase);
void trlStartRecord (MYSQL* conn, Project project, Phase phase);
void trlStopRecord (MYSQL* conn, Project project, Phase phase);
int trlIsPhaseFinished (MYSQL* conn, Project project, Phase phase);
void trlInsertInterruption (MYSQL* conn, Interruption interruption);
void trlUpdateInterruption (MYSQL* conn, Interruption interruption);
void trlStartInterruption (MYSQL* conn, Project project, Phase phase);
Interruption* trlGetUnendedInterruptionInLog (MYSQL* conn, Record record);
Interruption* trlGetUnendedInterruption (MYSQL* conn, Project project, Phase phase);
int trlIsPhaseInterrupted (MYSQL* conn, Project project, Phase phase);
void trlStopInterruption (MYSQL* conn, Project project, Phase phase, char* reason);
double trlGetInterruptionTime (Interruption* interruptions, long numOfInterruptions);
double trlGetDeltaTime (Record trlLog, Interruption* interruptions, long numOfInterruptions, double* interruptionTime);
unsigned long trlGetInterruptionsForTimeLog (MYSQL* conn, Record* record, Interruption** interruptions);
unsigned long trlGetInterruptionsForPhase (MYSQL* conn, Project project, Phase phase, Interruption** interruptions);
unsigned long trlRecordLogExecuteQuery (MYSQL* conn, char* query, Record** records);
Phase* trlGetCurrentPhase (MYSQL* conn, Project project);
int trlHasCurrentPhase (MYSQL* conn, Project project);
Phase* trlGetUpcomingPhase (MYSQL* conn, Project project);
void trlOnPostmortemFinish (MYSQL* conn, Project project, Phase phase);

/**DEFECT RECORDING LOG (DRL)**/
Defect drlConvertDefectRow (MYSQL_ROW row, unsigned int numberOfFields);
DefectType drlConvertDefectTypeRow (MYSQL_ROW row, unsigned int numberOfFields);
DefectDependency drlConvertDefectDependencyRow (MYSQL_ROW row, unsigned int numberOfFields);
unsigned long drlDefectCategoryExecuteQuery (MYSQL* conn, char* query, DefectType** types);
unsigned long drlDefectExecuteQuery (MYSQL* conn, char* query, Defect** defects);
FixPause drlConvertFixPauseRow (MYSQL_ROW row, unsigned int numberOfFields);
unsigned long drlFixPauseExecuteQuery (MYSQL* conn, char* query, FixPause** pauses);
unsigned long drlGetFixPausesForDefect (MYSQL* conn, Defect defect, FixPause** pauses);
unsigned long drlGetDefectCategories (MYSQL* conn, DefectType** defects);
void drlInsertDefect (MYSQL* conn, Defect defect);
void drlUpdateDefect (MYSQL* conn, Defect defect);
void drlInjectDefect (MYSQL* conn, Project project, Phase phase, char* description, DefectType type);
int drlIsDefectFixed (MYSQL* conn, Defect defect);
long drlGetDefectFixDeltaTime (MYSQL* conn, Defect defect);
void drlInsertFixPause (MYSQL* conn, FixPause pause);
void drlUpdateFixPause (MYSQL* conn, FixPause pause);
void drlPauseFixDefect (MYSQL* conn, Defect defect);
FixPause* drlGetUnendedFixPause (MYSQL* conn, Defect defect);
void drlUnpauseFixDefect (MYSQL* conn, Defect defect);
void drlFinishFixDefect (MYSQL* conn, Phase phase, Defect defect);
DefectType* drlGetDefectTypeById (MYSQL* conn, int typeId);
unsigned long drlGetDefectsForProject (MYSQL* conn, Project project, Defect** defects);
unsigned long drlGetDefectsInjectedAtProjectPhase (MYSQL* conn, Project project, Phase phase, Defect** defects);
unsigned long drlGetDefectsFixedAtProjectPhase (MYSQL*conn, Project project, Phase phase, Defect** defects);
int drlGetDefectInformationString (MYSQL* conn, Defect defect, char** defectStr);
void drlDisplayDefectInformation (MYSQL* conn, Defect defect);
void drlDisplayDefectLog (MYSQL* conn, Project project);
unsigned long drlGetUnfixedDefects (MYSQL* conn, Project project, Defect** defects);
int drlIsFixPauseEnded (MYSQL* conn, Defect defect);
unsigned long drlGetDefectsBeingFixed (MYSQL* conn, Project project, Defect** defects);
void drlStartFixDefect (MYSQL* conn, Phase phase, Defect defect);
Defect* drlGetDefectById (MYSQL* conn, int defectId);
unsigned long drlDefectDependencyExecuteQuery (MYSQL* conn, char* query, DefectDependency** dependencies);
Defect* drlGetDefectDependency (MYSQL* conn, Defect originalDefect);
void drlInsertDefectDependency (MYSQL* conn, DefectDependency defectDependency);
Defect* drlSelectDefectDependency (MYSQL* conn, Project project);

/**TEST REPORT TEMPLATE (TRT)**/
int trtCountDetails(MYSQL* conn, string300 query);
void trtConcatString(string300 query, int detail, int userProjectId, int testNum);
void trtSelectDetails(MYSQL* conn, string300 query);
int trtUpdateDetails(MYSQL* conn, string500 actualResults, int testNum);
void trtInputActualResults(MYSQL* conn, int userProjectId);
int trtInsertDetails(MYSQL* conn, TestReport testReport);

/**PROJECT IMPROVEMENT PLAN (PIP)**/
int pipCountDetails(MYSQL* conn, string300 query);
int pipUpdateDetails(MYSQL* conn, string800 notes, int piplanId);
void pipSelectDetails(MYSQL* conn, int details, int piplanId);
int pipInsertDetails(MYSQL* conn, int detail, int id, int piplanId,
                     string300 description);

/**USER-SECTION MANAGEMENT (USM)**/
void usmLogin(MYSQL* conn);
void usmSignup(MYSQL* conn);
void usmInputPassword(string20 usmPassword);
void usmInsertUser(MYSQL* conn, UserAccount usmAccount);
int usmDataExists(MYSQL* conn, char * basis, char * basis_column, char * basis_table);
int usmVerifyAccount(MYSQL* conn, UserAccount usmLoginDetails);
void usmChooseSections(MYSQL* conn, string3 usmSectionTemp);
void usmInsertSection(MYSQL* conn, Section usmSec);
void usmUpdateSection(MYSQL* conn, Section usmSec, string30 usmSectionOrig);
void usmEditSection(MYSQL* conn);
void usmViewSection(MYSQL* conn);

/**PROJECT MANAGEMENT (PM)**/
Project pmConvertRow(MYSQL_ROW row, unsigned int numberOfFields);
void pmGenerateProjects(MYSQL *conn, string40 userName);
void pmSetProjectDone(MYSQL* conn, string40 userName, int pspNum);
void pmSetProjectDoneWithId(MYSQL* conn, int userId, int pspNum);

/**PROJECT PLAN SUMMARY**/
void ppsSetPlannedTimeDB(MYSQL* conn, Project project, PlannedTime plannedTime);
int ppsPlannedTimeCheck(MYSQL* conn, PlannedTime plannedTime);
void ppsSetPlannedTime(MYSQL* conn, Project project);
PlannedTime ppsGetPlannedTime(MYSQL* conn, Project project);
void ppsSetPlannedLOCDB(MYSQL* conn, Project project, PlannedLOC plannedLOC);
int ppsPlannedLOCCheck(MYSQL* conn, PlannedLOC plannedLOC);
void ppsSetPlannedLOC(MYSQL* conn, Project project);
PlannedLOC ppsGetPlannedLOC(MYSQL* conn, Project project);
void ppsSetActualLOCDB(MYSQL* conn, Project project, ActualLOC actualLOC);
int ppsSetActualLOCCheck(MYSQL* conn, ActualLOC actualLOC);
void ppsSetActualLOC(MYSQL* conn, Project project);
ActualLOC ppsGetActualLOC(MYSQL* conn, Project project);
PlannedTime ppsGetActualTime(MYSQL* conn, Project project);
int ppsGetTotalActualTime(MYSQL* conn, Project project);
DefectsInjected ppsGetDefectsInjected(Project project);
DefectsRemoved ppsGetDefectsRemoved(Project project);
float ppsTotalPlannedLOC(PlannedLOC plannedLOC);
int ppsTotalActualLOC(ActualLOC actualLOC);
int ppsGetPlanIDFromProject(MYSQL* conn, Project project);

/**SIZE ESTIMATING TEMPLATE**/
ObjectType stObjectTypeConvertRow(MYSQL_ROW row, unsigned long numOfFields);
unsigned long stObjectTypeExecuteQuery(MYSQL* conn, char* query, ObjectType** objectTypes);
unsigned long stGetObjectTypes(MYSQL* conn, ObjectType** objectTypes);
ObjectType* stGetObjectTypeByTypeId(MYSQL* conn, int typeId);
SizeType stSizeTypeConvertRow(MYSQL_ROW row, unsigned long numOfFields);
unsigned long stSizeTypeExecuteQuery(MYSQL* conn, char* query, SizeType** sizeTypes);
unsigned long stGetSizeTypes(MYSQL* conn, SizeType** sizeTypes);
SizeType* stGetSizeTypeByTypeId(MYSQL* conn, int typeId);
ObjectSize stObjectSizeConvertRow(MYSQL_ROW row, unsigned long numOfFields);
unsigned long stObjectSizeExecuteQuery(MYSQL* conn, char* query, ObjectSize** objectSizes);
ObjectSize* stGetObjectSizeById(MYSQL* conn, int objectSizeId);
SizeEstimate stSizeEstimateConvertRow(MYSQL_ROW row, unsigned long numOfFields);
unsigned long stSizeEstimateExecuteQuery(MYSQL* conn, char* query, SizeEstimate** sizeEstimates);
SizeEstimate* stGetSizeEstimateById(MYSQL* conn, Project project);
unsigned long stNewObjectExecuteQuery(MYSQL* conn, char* query, NewObject** newObjects);
NewObject stNewObjectConvertRow(MYSQL_ROW row, unsigned long numOfFields);
unsigned long stGetNewObjectsForSizeEstimate(MYSQL* conn, SizeEstimate sizeEst, NewObject** newObjs);
unsigned long stReusedObjectExecuteQuery(MYSQL* conn, char* query, ReusedObject** reusedObjs);
ReusedObject stReusedObjectConvertRow(MYSQL_ROW row, unsigned long numOfFields);
unsigned long stGetReusedObjectsForSizeEstimate(MYSQL* conn, SizeEstimate sizeEst, ReusedObject** reusedObjs);
int stGetObjectSizeId(MYSQL* conn, int type, int size);
int stSetNewObject(MYSQL* conn, NewObject newObject, int type, int size);
int stSetReusedObject(MYSQL* conn, ReusedObject reusedObject);
float stGetTotalNewObjects(MYSQL* conn, SizeEstimate sizeEstimate);
int stGetReusedTotal(MYSQL* conn, SizeEstimate sizeEstimate);
int stHasSizeEstimate(MYSQL* conn, Project project);
unsigned long stSetSizeEstimate(MYSQL* conn, Project project);

/**SCHEDULE PLANNING TEMPLATE (SPT)**/
int sptGetTaskPlanIDFromProject(MYSQL* conn, Project project);
float sptGetHoursFromPhase(MYSQL* conn, Project project, int phaseId);
float sptGetTotalHoursPPS(MYSQL* conn, Project project, int phaseId);
float sptGetActualHoursFromPhase(MYSQL* conn, Project project, int phaseId);
sptRow* sptGetRows(MYSQL* conn, Project project);

/**INTEGRATION MODULE (IM)**/
//Reusable
int imSelectFromChoices(char* choices[], unsigned long numOfChoices, char* optionalPrompt);
int imSelectYesOrNo(char* optionalPrompt);
int imInputNumberRange (int lowerBound, int upperBound, char* optionalMessage, char* optionalErrorMessage);
unsigned int imDetermineNumberOfDaysFromMonthAndYear (int month, int year);

//Task Planning Template
unsigned long imTptConvertTaskPlanDisplays (MYSQL* conn, Project project, TaskPlanEntry* entries, unsigned long numOfEntries, TaskPlanDisplay** printedEntries);
void imTptDisplayTaskPlan (MYSQL* conn, Project project);
time_t imTptSelectMonthAndDay (TaskPlanEntry* previousEntry);
void imTptFillUpPhases (MYSQL* conn, TaskPlan plan);

//Defect Recording Log
DefectType* imDrlChooseCategory (MYSQL* conn, int cancelEnabled);
void imDrlPauseFixingDefect (MYSQL* conn, Project project, int confirmEnabled);
int imDrlSelectDefect (MYSQL* conn, Defect* defects, unsigned long numOfDefects, int cancelEnabled, char* optionalMessage);
void imDrlInjectDefectDependency (MYSQL* conn, unsigned long long defectDependentId);
void imDrlInjectDefect (MYSQL* conn, Project project, Phase curPhase);
void imDrlStartFixingDefect (MYSQL* conn, Project project, Phase curPhase);
int imDrlNoDefectOptions (MYSQL* conn, Project project, Phase curPhase);
void imDrlFinishFixingDefect (MYSQL* conn, Project project, Phase curPhase);
int imDrlHasDefectOptions (MYSQL* conn, Project project, Phase curPhase);
void imDrlViewDefectOptions (MYSQL* conn, Project project, Phase curPhase);
void imDrlDisplayCategories (MYSQL* conn);

//Phase retrieval interface
Phase* imPriPhaseSelect (MYSQL* conn, int cancelEnabled);

//Time recording log
int imTrlShowInterruptOptions (MYSQL* conn, Project project, Phase phase);
int imTrlStartLoggingOptions (MYSQL* conn, Project project, Phase phase);
int imTrlStopLoggingOptions (MYSQL* conn, Project project, Phase phase, int phaseDone);
void imTrlRecordTime (MYSQL* conn, Project project, Phase phase);
void imTrlDisplayTimeRecordingLog (MYSQL* conn, Project project);

//User and Section Management
void imUsmSectionManagement(MYSQL* conn);

//General UI
void imViewForms(MYSQL* conn, Project project);
int imDisplayMain();
int imPlanningOptions(MYSQL* conn, Project project, Phase phase);
int imViewPhaseSpecificOptions(MYSQL* conn, Project project, Phase phase);

//Project management
void imPmSelectProjectForViewForms(MYSQL* conn, Project* projects, unsigned long numberOfProjects);
void imPmViewProjectDetails(MYSQL* conn, MYSQL_RES *res);
void imPmViewProjects(MYSQL *conn, string40 usmUserName);

//Test Report Template
void imTrtGetInput(MYSQL* conn, int userProjectId);
void imTrtMenu(MYSQL* conn, Project project, Phase phase);
void imTrtDisplayReport(MYSQL* conn, int userProjectId, int testNum);
void imTrtDisplayAllReports(MYSQL* conn, int userProjectId);

//Size estimating template
void imStViewSizeEstimatingTemplate(MYSQL* conn, Project project);
void imStMain(MYSQL* conn, Project project);

//Project plan summary
void imPpsDisplayProjectPlanSummary(MYSQL* conn, Project project);

//Process improvement proposal
void imPipInputNotes(MYSQL* conn, ProcImpPlan procImpPlan);
void imPipInputProposal(MYSQL* conn, int piplanId);
void imPipInputProblem(MYSQL* conn, int piplanId);
void imPipMenu(MYSQL* conn, int userProjectId);
void imPipDisplayPlan(MYSQL* conn, int piplanId);

/**TESTS AND MAIN**/
int main();

/**UTILITY FUNCTIONS**/
void utilEscapeStringValue (char** stringValue) {
	char* actualString = *stringValue;

	if (actualString == NULL) {
		return;
	}

	unsigned long length = strlen(actualString);
	unsigned long stringCtr;
	char* newString = NULL;

	for (stringCtr = 0; stringCtr < length; stringCtr++) {
		char curChar = *(actualString + stringCtr);
		if (curChar == '\'') {
			
			if (newString == NULL) {
				length ++;
				newString = malloc(sizeof(char)*(length+1));
			}
			else {
				length ++;
				newString = realloc(newString, sizeof(char)*(length+1));
			}

			if (newString == NULL) {
				fprintf(stderr, "%s\n", "Memory for allocation has run out");
			}
			else {
				strncpy(newString, actualString, stringCtr + 1);
				strcat(newString, "'");
			}

		}
	}
    
    if (newString != NULL) {
        strcat(newString, actualString + stringCtr);
        free(actualString);
        actualString = newString;
    }
}

void utilFormatTime (const time_t time, char** timeString) {
    if (time == 0) {
        char* nothing = "(none)";
        unsigned long bufferSize = strlen(nothing) + 1;
        *timeString = malloc(sizeof(char) * bufferSize);
        strncpy(*timeString, nothing, bufferSize);
    }
    else {
        *timeString = malloc(sizeof(char) * 20);
        strftime(*timeString, 20, "%D - %H:%M", localtime(&time));
    }
}

time_t utilGetCurrentCalendarDate (int* month, int* day, int* year) {
    //get current day
    time_t curNanos = time(NULL);
    struct tm* curTime = localtime(&curNanos);
    *month = curTime->tm_mon;
    *day = curTime->tm_mday;
    *year = curTime->tm_year;
    return curNanos;
}

void utilFormatDate (const time_t time, char** timeString) {
	if (time == 0) {
		char* noneTime = "(none)";
		unsigned long bufferSize = strlen(noneTime)+1;
		*timeString = malloc(sizeof(char)*bufferSize);
		strncpy(*timeString, noneTime, bufferSize);
	}
	else {
		*timeString = malloc(sizeof(char)*15);
		strftime(*timeString, 15, "%m/%d/%Y", localtime(&time));
	}
}

void utilStringifyFile (FILE* filePtr, char** stringified) {
    if (filePtr == NULL) {
        *stringified = NULL;
        return;
    }
    else {
        char* tempString = malloc(MAX_ARBITRARY_STRING_LENGTH * sizeof(char));
        char* entireFile = NULL;
        int initialAlloc = 1;
        
        while (!feof(filePtr)) {
            if (initialAlloc == 1) {
                initialAlloc = 0;
                entireFile = malloc(sizeof(char) * 2);
                *entireFile = '\0';
            }
            else {
                entireFile = realloc(entireFile, (strlen(entireFile) + strlen(tempString) + 1) * sizeof(char));
                strcat(entireFile, tempString);
            }
            
            fgets(tempString, MAX_ARBITRARY_STRING_LENGTH, filePtr);
        }
        
        *stringified = entireFile;
    }
}

/**DATABASE CONNECTION**/

void utilPrintError (MYSQL* conn, char* message) {
	fprintf(stderr, "%s\n", message);

	if (conn != NULL) {
		fprintf(stderr, "Error %u - %s\n", mysql_errno(conn), mysql_error(conn));
	}
}

MYSQL *dbConnectDatabase (char* server, char* username, char* password, char* dbName,
	unsigned int portNumber, char* socketName, unsigned int flags) {

	MYSQL *conn;

	conn = mysql_init(NULL);

	if (conn == NULL) {
      printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
      //exit(1);
  	}

  	if (mysql_real_connect(conn, server, username, password, dbName, portNumber, socketName, flags) == NULL) {
      printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
      //exit(1);
  	}

  	return conn;

}

void dbDisconnectDatabase (MYSQL* mySqlInstance) {
	mysql_close(mySqlInstance);
}

MYSQL* dbConnectDefaultDatabase (char* username, char* password) {
  MYSQL *conn;

  conn = dbConnectDatabase("localhost", username, 
          password, (char*)DB_NAME, 0, NULL, 0);

  return conn;
}

/**Returns 0 if the retrieval failed; 1 if successful**/
int dbRetrieveLogin(char** userName, char** password) {
    FILE* filePtr = fopen("creds", "rb");
    
    if (filePtr == NULL) {
        return 0;
    }
    
    unsigned long userNameSize = 0;
    unsigned long passwordSize = 0;
    fread(&userNameSize, sizeof(unsigned long), 1, filePtr);
    fread(&passwordSize, sizeof(unsigned long), 1, filePtr);
    
    *userName = malloc(sizeof(char) * userNameSize);
    *password = malloc(sizeof(char) * passwordSize);
    fread(*userName, sizeof(char), userNameSize, filePtr);
    fread(*password, sizeof(char), passwordSize, filePtr);
    fclose(filePtr);
    
    return 1;
}

void dbStoreLogin(char* userName, char* password) {
    FILE* filePtr = fopen("creds", "wb");
    unsigned long userNameSize = strlen(userName) + 1;
    unsigned long passwordSize = strlen(password) + 1;
    fwrite(&userNameSize, sizeof(unsigned long), 1, filePtr);
    fwrite(&passwordSize, sizeof(unsigned long), 1, filePtr);
    fwrite(userName, sizeof(char), userNameSize, filePtr);
    fwrite(password, sizeof(char), passwordSize, filePtr);
    fclose(filePtr);
}

void dbAskLogin(char** userName, char** password) {
    if (!dbRetrieveLogin(userName, password)) {
        printf("Please enter your MYSQL user name and password.\n(Please make sure you have privileges to create databases.)\n\n");
        *userName = malloc(sizeof(char) + 41);
        printf("User name: ");
        utilFlush();
        scanf("%s", *userName);
        printf("Password: ");
        *password = malloc(sizeof(char) + 21);
        usmInputPassword(*password);
    }
}

int dbCheckDatabaseExists (MYSQL* conn, char* dbName) {
    char query[1000];
    snprintf(query, 1000, "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '%s'", DB_NAME);
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Could not determine whether schema exists...");
        return 0;
    }
    else {
        MYSQL_RES* result = mysql_store_result(conn);
        if (result == NULL) {
            utilPrintError(conn, "Could not determine whether schema exists...");
            return 0;
        }
        unsigned long rows = mysql_num_rows(result);
        
        if (rows == 0) {
            return 0;
        }
        else {
            return 1;
        }
    }
}

void dbReadAndStringify (const char* fileName, char** fileContents) {
    FILE* file = fopen(fileName, "rb");
    utilStringifyFile(file, fileContents);
    fclose(file);
}

int dbExecuteSchemaFile (MYSQL* conn, const char* fileName) {
    printf("Executing %s\n", fileName);
    char* fileContents = NULL;
    dbReadAndStringify(fileName, &fileContents);
    
    char* currentStatement = NULL;
    currentStatement = strtok(fileContents, ";");
    int success = 1;
    
    if (currentStatement == NULL) {
        success = 0;
    }
    else {
        do {
            printf("Executing:\n%s\n", currentStatement);
            if (mysql_query(conn, currentStatement) != 0) {
                //accept empty queries
                if (mysql_errno(conn) != 1065) {
                    success = 0;
                    break;
                }
            }
        } while ((currentStatement = strtok(NULL, ";")) != NULL);
    }
    
    free(fileContents);
    if (!success) {
        utilPrintError(conn, "Could not create database...");
    }
    return success;
}

int dbExecuteSchemaFiles (MYSQL* conn) {
    int status = 1;
    const char* files[7] = {CREATE_SCRIPT_FILE, PHASE_FILE, DEFECT_FILE, SIZE_FILE, OBJECT_FILE, OBJECT_SIZE_FILE, SECTION_FILE};
    
    int fileCtr;
    for (fileCtr = 0; fileCtr < 7; fileCtr++) {
        status = dbExecuteSchemaFile(conn, files[fileCtr]);
        
        if (!status) {
            utilPrintError(conn, "DB population failed... Cannot execute program. Please check your user privileges.");
            
            char query[1000];
            snprintf(query, 1000, "DROP SCHEMA IF EXISTS %s;", DB_NAME);
            if (mysql_query(conn, query) != 0) {
                utilPrintError(conn, "Could not drop database. Please ask the administrator for help.");
            }
            return status;
        }
    }
    
    return status;
}

/**PHASE RETRIEVAL**/

Phase priConvertRow (MYSQL_ROW row, unsigned int numberOfFields) {
	int fieldCtr;
	Phase phase;

	for (fieldCtr = 0; fieldCtr < numberOfFields; fieldCtr++) {
		char* rowData = row[fieldCtr];

		if (fieldCtr == 0) {
			//PHASE ID
			phase.phaseId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 1) {
			//PHASE NAME
            if (rowData != NULL) {
            	phase.phaseName = malloc(sizeof(char) * (strlen(rowData) + 1));
                strcpy(phase.phaseName, rowData);
            }
            else {
                phase.phaseName = NULL;
            }
		}
		else {
			utilPrintError(NULL, "Unexpected column.");
		}
	}

	return phase;
}

unsigned long priGetPhases (MYSQL* conn, Phase** phase) {
	MYSQL_RES* resultSet;
	char query[300];

	snprintf(query, 300, "SELECT * FROM %s;", PhaseTable.tableName);

	if (mysql_query(conn, query) != 0) {
		utilPrintError(NULL, "The query is invalid.");
	}
	else {
		resultSet = mysql_store_result(conn);
		if (resultSet == NULL) {
			utilPrintError(conn, "The result set could not be stored.");
		}
		else {
			const unsigned long numberOfRows = (unsigned long)mysql_num_rows(resultSet);
			Phase* results = malloc(sizeof(Phase) * numberOfRows);

			MYSQL_ROW row;
			unsigned int rowCtr = 0;
			unsigned int numOfFields = mysql_num_fields(resultSet);

			while ((row = mysql_fetch_row(resultSet)) != NULL) {
				*(results + rowCtr) = priConvertRow(row, numOfFields);
				rowCtr++;
			}

			mysql_free_result(resultSet);
			*phase = results;
			return numberOfRows;
		}
	}
	return 0;
}

unsigned long priExecuteQuery (MYSQL* conn, char* query, Phase** phases) {
    if (query != NULL) {
        if (mysql_query(conn, query) != 0) {
            utilPrintError(conn, "The query could not be executed. Phases could not be retrieved");
        }
        else {
            MYSQL_RES* results = mysql_store_result(conn);
            unsigned long numberOfRows = mysql_num_rows(results);
            Phase* phasesResult = malloc(sizeof(Phase) * numberOfRows);
            
            unsigned int numberOfFields = mysql_num_fields(results);
            
            MYSQL_ROW row;
            unsigned int rowCtr = 0;
            while ((row = mysql_fetch_row(results)) != NULL) {
                *(phasesResult + rowCtr) = priConvertRow(row, numberOfFields);
                rowCtr++;
            }
            
            mysql_free_result(results);
            *phases = phasesResult;
            return numberOfRows;
        }
    }
    else {
        utilPrintError(conn, "Query could not execute. The phases could not be retrieved.");
    }
    
    return 0;
}

Phase* priGetPhaseById (MYSQL* conn, int phaseId) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;", PhaseTable.tableName, PhaseTable.phaseId, phaseId);
    
    Phase* phases = NULL;
    unsigned long numberOfResults = priExecuteQuery(conn, query, &phases);
    
    if (numberOfResults == 0) {
        return NULL;
    }
    else {
        return phases;
    }
}

/**TASK PLANNING TEMPLATE**/

TaskPlan tptConvertTaskPlanRow (MYSQL_ROW row, unsigned int numberOfFields) {
	int fieldCtr;
	TaskPlan plan;
	
	for (fieldCtr = 0; fieldCtr < numberOfFields; fieldCtr++) {
		char* rowData = row[fieldCtr];
        
		if (fieldCtr == 0) {
			//task plan id
			plan.taskPlanId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 1) {
			//user project id
			plan.userProjectId = rowData != NULL ? atoi(rowData) : -1;
		}
		else {
			utilPrintError(NULL, "Unexpected column.");
		}
	}
    
	return plan;
}

unsigned long tptExecuteTaskPlanQuery (MYSQL* conn, char* query, TaskPlan** plans) {
	if (query != NULL) {
		if (mysql_query(conn, query) != 0) {
			utilPrintError(conn, "Could not execute query. Task plan not retrieved.");
		}
		else {
			MYSQL_RES* results = mysql_store_result(conn);
			unsigned long numberOfRows = mysql_num_rows(results);
            
			if (numberOfRows > 0) {
				TaskPlan* planEntries = malloc(sizeof(TaskPlan) * numberOfRows);
				unsigned int numberOfFields = mysql_num_fields(results);
				MYSQL_ROW row;
				unsigned int rowCtr = 0;
				while ((row = mysql_fetch_row(results)) != NULL) {
					*(planEntries + rowCtr) = tptConvertTaskPlanRow(row, numberOfFields);
					rowCtr++;
				}
				*plans = planEntries;
			}
            
			mysql_free_result(results);
			
			return numberOfRows;
		}
	}
	else {
		utilPrintError(conn, "Unable to execute task plan table query.");
	}
	*plans = NULL;
	return 0;
}

unsigned long tptGetTaskPlanById(MYSQL* conn, unsigned long long id, TaskPlan** plans) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %lld;", TaskPlanTable.tableName, TaskPlanTable.taskPlanId, id);
    return tptExecuteTaskPlanQuery(conn, query, plans);
}

TaskPlan* tptCreateTaskPlan(MYSQL* conn, Project project) {
	char insert[300];
    snprintf(insert, 300, "INSERT INTO %s (%s) VALUES (%d);", TaskPlanTable.tableName, TaskPlanTable.userProjectId, project.userProjectId);
    
    if (mysql_query(conn, insert) != 0) {
        utilPrintError(conn, "The insert statement was not executed...");
        return NULL;
    }
    else {
        TaskPlan* plan = NULL;
        tptGetTaskPlanById(conn, mysql_insert_id(conn), &plan);
        return plan;
    }
}

TaskPlan* tptGetTaskPlanForProject (MYSQL* conn, Project project) {
	char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;", TaskPlanTable.tableName, TaskPlanTable.userProjectId, project.userProjectId);
    
    TaskPlan* plans = NULL;
    tptExecuteTaskPlanQuery(conn, query, &plans);
    return plans;
}

TaskPlanEntry tptConvertTaskPlanEntryRow(MYSQL_ROW row, unsigned int numberOfFields) {
	int fieldCtr;
	TaskPlanEntry entry;
    
	for (fieldCtr = 0; fieldCtr < numberOfFields; fieldCtr++) {
		char* rowData = row[fieldCtr];
		
		if (fieldCtr == 0) {
			//ENTRY ID
			entry.entryId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 1) {
			//TASK PLAN ID
			entry.taskPlanId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 2) {
			//PHASE ID
			entry.phaseId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 3) {
			//DATE PLANNED
			entry.datePlanned = rowData != NULL ? atol(rowData) : 0;
		}
		else if (fieldCtr == 4) {
			//DATE ACCOMPLISHED
			entry.dateAccomplished = rowData != NULL ? atol(rowData) : 0;
		}
		else {
			utilPrintError(NULL, "Unexpected column!");
		}
	}
    
	return entry;
}

unsigned long tptExecuteTaskPlanEntryQuery(MYSQL* conn, char* query, TaskPlanEntry** entries) {
	if (query != NULL) {
		if (mysql_query(conn, query) != 0) {
			utilPrintError(conn, "Could not execute query. Task plan entry not retrieved.");
		}
		else {
			MYSQL_RES* results = mysql_store_result(conn);
			unsigned long numberOfRows = mysql_num_rows(results);
            
			if (numberOfRows > 0) {
				TaskPlanEntry* planEntries = malloc(sizeof(TaskPlanEntry) * numberOfRows);
				unsigned int numberOfFields = mysql_num_fields(results);
				MYSQL_ROW row;
				unsigned int rowCtr = 0;
				while ((row = mysql_fetch_row(results)) != NULL) {
					*(planEntries + rowCtr) = tptConvertTaskPlanEntryRow(row, numberOfFields);
					rowCtr++;
				}
				*entries = planEntries;
			}
            
			mysql_free_result(results);
			
			return numberOfRows;
		}
	}
	else {
		utilPrintError(conn, "Could not execute query. Task plan entry not retrieved.");
	}
	*entries = NULL;
	return 0;
}

unsigned long tptGetTaskPlanEntryById(MYSQL* conn, int id, TaskPlanEntry** entries) {
	char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;", TaskPlanEntryTable.tableName, TaskPlanEntryTable.entryId, id);
    return tptExecuteTaskPlanEntryQuery(conn, query, entries);
}

void tptInsertTaskPlanEntry (MYSQL* conn, TaskPlanEntry entry) {
	char query[500];
    snprintf(query, 500, "INSERT INTO %s (%s, %s, %s, %s) VALUES (%d, %d, %ld, %ld);", TaskPlanEntryTable.tableName, TaskPlanEntryTable.taskPlanId, TaskPlanEntryTable.phaseId, TaskPlanEntryTable.datePlanned, TaskPlanEntryTable.dateAccomplished, entry.taskPlanId, entry.phaseId, entry.datePlanned, entry.dateAccomplished);
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Cannot execute insert statement.");
    }
}

unsigned long tptGetTaskPlanEntriesFromPlan (MYSQL* conn, TaskPlan plan, TaskPlanEntry** entries) {
	char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;", TaskPlanEntryTable.tableName, TaskPlanEntryTable.taskPlanId, plan.taskPlanId);
    return tptExecuteTaskPlanEntryQuery(conn, query, entries);
}

unsigned long tptGetTaskPlanEntriesFromProject (MYSQL* conn, Project project, TaskPlanEntry** entries) {
	TaskPlan* plan = tptGetTaskPlanForProject (conn, project);
	if (plan == NULL) {
        *entries = NULL;
		return 0;
	}
	else {
		return tptGetTaskPlanEntriesFromPlan(conn, *plan, entries);
	}
}

TaskPlanEntry* tptGetProjectTaskPlanEntryWithPhase (MYSQL* conn, TaskPlan plan, Phase phase) {
	char query[500];
    snprintf(query, 500, "SELECT * FROM %s WHERE %s = %d AND %s = %d;", TaskPlanEntryTable.tableName, TaskPlanEntryTable.taskPlanId, plan.taskPlanId, TaskPlanEntryTable.phaseId, phase.phaseId);
    TaskPlanEntry* entries = NULL;
    tptExecuteTaskPlanEntryQuery(conn, query, &entries);
    return entries;
}

TaskPlanEntry* tptGetTaskPlanEntryWithProjectAndPhase (MYSQL* conn, Project project, Phase phase) {
	TaskPlan* plan = tptGetTaskPlanForProject (conn, project);
	if (plan == NULL) {
		utilPrintError(conn, "Cannot find the plan for this project, thus cannot get the task plan entries for the plan.");
	}
	else {
		TaskPlanEntry* planEntry = tptGetProjectTaskPlanEntryWithPhase(conn, *plan, phase);
		return planEntry;
	}
	return NULL;
}

void tptUpdateTaskPlanEntry (MYSQL* conn, TaskPlanEntry entry) {
    //only updates date planned and date accomplished
	char query[400];
    snprintf(query, 400, "UPDATE %s SET %s = %ld, %s = %ld WHERE %s = %d;", TaskPlanEntryTable.tableName, TaskPlanEntryTable.dateAccomplished, entry.dateAccomplished, TaskPlanEntryTable.datePlanned, entry.datePlanned, TaskPlanEntryTable.entryId, entry.entryId);
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Could not execute UPDATE statement.");
    }
}

void tptUpdateTaskPlanEntryOnPhaseDone (MYSQL* conn, Project project, Phase phase, time_t stopTime) {
	//get the original task plan entry with the phase that was finished
	TaskPlanEntry* entry = tptGetTaskPlanEntryWithProjectAndPhase(conn, project, phase);
	
	if (entry == NULL) {
		utilPrintError(conn, "Cannot update a record that does not exist!");
	}
	else {
		entry->dateAccomplished = stopTime;
		tptUpdateTaskPlanEntry(conn, *entry);
        
		free(entry);
	}
}

//returns 0 if there is no task planning template
int tptCheckTaskPlanningTemplate(MYSQL* conn, Project project) {
    TaskPlan* plan = NULL;
    if ((plan = tptGetTaskPlanForProject(conn, project)) == NULL) {
        return 0;
    }
    else {
        return 1;
    }
}

/**TIME RECORDING LOG**/

Interruption trlConvertInterruptionRow(MYSQL_ROW row, unsigned int numberOfFields) {
	Interruption interruption;

	int fieldCtr;
	for (fieldCtr = 0; fieldCtr < numberOfFields; fieldCtr++) {
		char* rowData = row[fieldCtr];

		if (fieldCtr == 0) {
			//interruption id
			interruption.interruptionId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 1) {
			//log id
			interruption.logId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 2) {
			//start time
			interruption.startTime = rowData != NULL ? atol(rowData) : 0;
		}
		else if (fieldCtr == 3) {
			//end time
			interruption.endTime = rowData != NULL ? atol(rowData) : 0;
		}
		else if (fieldCtr == 4) {
			//reason
			if (rowData != NULL) {
				interruption.reason = malloc(sizeof(char) * (strlen(rowData) + 1));
				strcpy(interruption.reason, rowData);
			}
			else {
				interruption.reason = NULL;
			}
		}
		else {
			utilPrintError(NULL, "Unexpected column during conversion to interruption object.");
		}
	}

	return interruption;

}

void trlInsertRecord (MYSQL* conn, Record timeLog) {
	if (timeLog.userProjectId < 1) {
		utilPrintError(NULL, "The project specified is not valid.");
		return;
	}

	char insertQuery[300];

	if (timeLog.reason != NULL) {
		//escape the string in the reason field
		utilEscapeStringValue(&(timeLog.reason));
        
		snprintf(insertQuery, 300, "INSERT INTO %s (%s, %s, %s, %s, %s) VALUES (%d, %d, %ld, %ld, '%s');",
			RecordTable.tableName, 
			RecordTable.userProjectId, RecordTable.phaseId, RecordTable.startTime, RecordTable.endTime, RecordTable.remarks,
			timeLog.userProjectId, timeLog.phaseId, timeLog.startTime, timeLog.endTime, timeLog.reason);
	}
	else {
		snprintf(insertQuery, 300, "INSERT INTO %s (%s, %s, %s, %s) VALUES (%d, %d, %ld, %ld);",
			RecordTable.tableName, 
			RecordTable.userProjectId, RecordTable.phaseId, RecordTable.startTime, RecordTable.endTime,
			timeLog.userProjectId, timeLog.phaseId, timeLog.startTime, timeLog.endTime);
	}	

	if (mysql_query(conn, insertQuery) != 0) {
		utilPrintError(conn, "INSERT statement failed");
	}

}

void trlUpdateRecord (MYSQL* conn, Record record) {
	if (record.userProjectId < 1) {
		utilPrintError(NULL, "The project specified is not valid.");
		return;
	}

	char updateQuery[300];

	if (record.reason != NULL) {
		//escape the string in the reason field
		utilEscapeStringValue(&(record.reason));

		snprintf(updateQuery, 300, "UPDATE %s SET %s = %ld, %s = %ld, %s = '%s' WHERE %s = %d;",
			RecordTable.tableName, 
			RecordTable.startTime, record.startTime,
			RecordTable.endTime, record.endTime,
			RecordTable.remarks, record.reason,
			RecordTable.logId, record.logId);	
	}
	else {
		snprintf(updateQuery, 300, "UPDATE %s SET %s = %ld, %s = %ld WHERE %s = %d;",
			RecordTable.tableName, 
			RecordTable.startTime, record.startTime,
			RecordTable.endTime, record.endTime,
			RecordTable.logId, record.logId);	
	}
	

	if (mysql_query(conn, updateQuery) != 0) {
		utilPrintError(conn, "UPDATE statement failed");
	}
}

Record trlConvertRecordRow (MYSQL_ROW row, unsigned int numberOfFields) {
	int fieldCtr;
	Record record;

	for (fieldCtr = 0; fieldCtr < numberOfFields; fieldCtr++) {
		char* rowData = row[fieldCtr];

		if (fieldCtr == 0) {
			//log id
			record.logId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 1) {
			//user project id
			record.userProjectId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 2) {
			//phase id
			record.phaseId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 3) {
			//start time
			record.startTime = rowData != NULL ? atol(rowData) : 0;
		}
		else if (fieldCtr == 4) {
			//end time
			record.endTime = rowData != NULL ? atol(rowData) : 0;
		}
		else if (fieldCtr == 5) {
			//reason
			if (rowData != NULL) {
				record.reason = malloc(sizeof(char) * (strlen(rowData) + 1));
				strcpy(record.reason, rowData);
			}
			else {
				record.reason = NULL;
			}
		}
		else {
			utilPrintError(NULL, "Unexpected column.");
		}
	}

	return record;
}

unsigned long trlGetTimeLogsForProject (MYSQL* conn, Project project, Record** records) {
	MYSQL_RES* resultSet;
	char query[300];

	snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;",
		RecordTable.tableName,
		RecordTable.userProjectId, project.userProjectId);

	if (mysql_query(conn, query) != 0) {
		utilPrintError(NULL, "The query is invalid. Can't retrieve time logs for project.");
	}
	else {
		resultSet = mysql_store_result(conn);
		if (resultSet == NULL) {
			utilPrintError(conn, "The result set could not be stored.");
		}
		else {
			const unsigned long numberOfRows = (unsigned long)mysql_num_rows(resultSet);
			Record* results = malloc(sizeof(Record) * numberOfRows);

			MYSQL_ROW row;
			unsigned int rowCtr = 0;
			unsigned int numOfFields = mysql_num_fields(resultSet);

			while ((row = mysql_fetch_row(resultSet)) != NULL) {
				*(results + rowCtr) = trlConvertRecordRow(row, numOfFields);
			}

			mysql_free_result(resultSet);
			*records = results;
			return numberOfRows;
		}
	}

	return 0;
}

Record* trlGetTimeLogForPhase (MYSQL* conn, Project project, Phase phase) {
	MYSQL_RES* resultSet;
	char query[300];

	snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d AND %s = %d;",
		RecordTable.tableName,
		RecordTable.userProjectId, project.userProjectId,
		RecordTable.phaseId, phase.phaseId);

	if (mysql_query(conn, query) != 0) {
		utilPrintError(NULL, "The query is invalid. Can't retrieve time log for specific phase.");
	}
	else {
        resultSet = mysql_store_result(conn);
        if (resultSet == NULL) {
            utilPrintError(conn, "Query failed. Couldn't retrieve time logs per phase");
        }
        else {
            const unsigned long numberOfRows = (unsigned long)mysql_num_rows(resultSet);
            Record* results = malloc(sizeof(Record) * numberOfRows);
            
            MYSQL_ROW row;
            unsigned int rowCtr = 0;
            unsigned int numOfFields = mysql_num_fields(resultSet);
            
            while ((row = mysql_fetch_row(resultSet)) != NULL) {
                *(results + rowCtr) = trlConvertRecordRow(row, numOfFields);
                rowCtr++;
            }
            
            mysql_free_result(resultSet);
            
            if (numberOfRows == 0) {
                return NULL;
            }
            else {
                return results;
            }
        }
	}

	return NULL;
}

void trlStartRecord (MYSQL* conn, Project project, Phase phase) {
	Record* newTimeLog = malloc(sizeof(Record));
	newTimeLog->userProjectId = project.userProjectId;
	newTimeLog->phaseId = phase.phaseId;
	newTimeLog->startTime = time(NULL);
	newTimeLog->endTime = 0;
	newTimeLog->reason = NULL;
	trlInsertRecord(conn, *newTimeLog);
	free(newTimeLog);
}

void trlStopRecord (MYSQL* conn, Project project, Phase phase) {
	Record* record = trlGetTimeLogForPhase(conn, project, phase);

	if (record == NULL) {
		utilPrintError(NULL, "The time recording log to be updated is not found.");
		return;
	}
	else {
        time_t endTime = time(NULL);
		Record previousRecord = *record;
		previousRecord.endTime = endTime;
		trlUpdateRecord(conn, previousRecord);
		free(record);
        
        //updates corresponding task plan template entry for the phase
        tptUpdateTaskPlanEntryOnPhaseDone(conn, project, phase, endTime);
        
        //update the project status if the phase is postmortem
        trlOnPostmortemFinish(conn, project, phase);
	}
}

int trlIsPhaseFinished (MYSQL* conn, Project project, Phase phase) {
    Record* associatedRecord = trlGetTimeLogForPhase(conn, project, phase);
    
    if (associatedRecord != NULL) {
        if (associatedRecord->endTime == 0) {
            return 0;
        }
        else {
            return 1;
        }
    }
    
    return 0;
}

void trlInsertInterruption (MYSQL* conn, Interruption interruption) {
	if (interruption.logId < 1) {
		utilPrintError(NULL, "The time log specified is not valid.");
		return;
	}

	char insertQuery[300];

	if (interruption.reason != NULL) {
		//escape the string in the reason field
		utilEscapeStringValue(&(interruption.reason));
		snprintf(insertQuery, 300, "INSERT INTO %s (%s, %s, %s, %s) VALUES (%d, %ld, %ld, '%s');",
			InterruptionTable.tableName,
			InterruptionTable.logId, InterruptionTable.startTime, InterruptionTable.endTime, InterruptionTable.reason,
			interruption.logId, interruption.startTime, interruption.endTime, interruption.reason);
	}
	else {
		snprintf(insertQuery, 300, "INSERT INTO %s (%s, %s, %s) VALUES (%d, %ld, %ld);",
			InterruptionTable.tableName,
			InterruptionTable.logId, InterruptionTable.startTime, InterruptionTable.endTime,
			interruption.logId, interruption.startTime, interruption.endTime);
	}

	if (mysql_query(conn, insertQuery) != 0) {
		utilPrintError(NULL, "INSERT statement failed");
	}
}

void trlUpdateInterruption (MYSQL* conn, Interruption interruption) {
	if (interruption.logId < 1) {
		utilPrintError(NULL, "The time log specified is not valid.");
		return;
	}

	char updateQuery[300];

	if (interruption.reason != NULL) {
		//escape the string in the reason field
		utilEscapeStringValue(&(interruption.reason));
		snprintf(updateQuery, 300, "UPDATE %s SET %s = %ld, %s = %ld, %s = '%s' WHERE %s = %d;",
			InterruptionTable.tableName,
			InterruptionTable.startTime, interruption.startTime,
			InterruptionTable.endTime, interruption.endTime,
			InterruptionTable.reason, interruption.reason,
			InterruptionTable.interruptionId, interruption.interruptionId);
	}
	else {
		snprintf(updateQuery, 300, "UPDATE %s SET %s = %ld, %s = %ld WHERE %s = %d;",
			InterruptionTable.tableName,
			InterruptionTable.startTime, interruption.startTime,
			InterruptionTable.endTime, interruption.endTime,
			InterruptionTable.interruptionId, interruption.interruptionId);
	}

	if (mysql_query(conn, updateQuery) != 0) {
		utilPrintError(NULL, "UPDATE statement failed");
	}
}

void trlStartInterruption (MYSQL* conn, Project project, Phase phase) {
	Interruption* newInterruption = malloc(sizeof(Interruption));
	newInterruption->startTime = time(NULL);
	newInterruption->endTime = 0;
	newInterruption->reason = NULL;

	//retrieve trl entry for current phase and project
	Record* trlEntry = trlGetTimeLogForPhase(conn, project, phase);

	if (trlEntry == NULL) {
		utilPrintError(NULL, "The associated time log for this interruption could not be found.");
	}
	else {
		Record record = *trlEntry;
		newInterruption->logId = record.logId;

		trlInsertInterruption(conn, *newInterruption);
		free(trlEntry);
	}

	free(newInterruption);
}

Interruption* trlGetUnendedInterruptionInLog (MYSQL* conn, Record record) {
	MYSQL_RES* resultSet;
	char query[300];

	snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d AND %s = %d;",
		InterruptionTable.tableName,
		InterruptionTable.logId, record.logId,
		InterruptionTable.endTime, 0);

	if (mysql_query(conn, query) != 0) {
		utilPrintError(NULL, "The query is invalid. Can't retrieve unended interruption");
	}
	else {
        resultSet = mysql_store_result(conn);
        
        if (resultSet == NULL) {
            utilPrintError(conn, "Query failed. Unable to get unended interruption based on record.");
        }
        else {
            const unsigned long numberOfRows = (unsigned long)mysql_num_rows(resultSet);
            Interruption* results = malloc(sizeof(Interruption) * numberOfRows);
            
            MYSQL_ROW row;
            unsigned int rowCtr = 0;
            unsigned int numOfFields = mysql_num_fields(resultSet);
            
            while ((row = mysql_fetch_row(resultSet)) != NULL) {
                *(results + rowCtr) = trlConvertInterruptionRow(row, numOfFields);
                rowCtr++;
            }
            
            mysql_free_result(resultSet);
            
            if (numberOfRows == 0) {
                return NULL;
            }
            else {
                return results;			
            }
        }
	}

	return NULL;
}

Interruption* trlGetUnendedInterruption (MYSQL* conn, Project project, Phase phase) {
	Record* phaseLog = trlGetTimeLogForPhase(conn, project, phase);

	if (phaseLog == NULL) {
		return NULL;
	}
	else {
		Interruption* unendedInterruption = trlGetUnendedInterruptionInLog(conn, *phaseLog);
		free(phaseLog);
		return unendedInterruption;
	}
}

int trlIsPhaseInterrupted (MYSQL* conn, Project project, Phase phase) {
	Interruption* interruption = trlGetUnendedInterruption(conn, project, phase);
	if (interruption == NULL)
		return 0; //false
	else {
		free(interruption);
		return 1; //true
	}
}

void trlStopInterruption (MYSQL* conn, Project project, Phase phase, char* reason) {
	Interruption* lastInterruption = trlGetUnendedInterruption(conn, project, phase);

	if (lastInterruption == NULL) {
		utilPrintError(NULL, "The associated interruption to be updated with a stop time cannot be found.");
		return;
	}
	else {
		Interruption updatedInterruption = *lastInterruption;
		updatedInterruption.endTime = time(NULL);
		updatedInterruption.reason = reason;		
		trlUpdateInterruption(conn, updatedInterruption);
		free(lastInterruption);
	}
}

double trlGetInterruptionTime (Interruption* interruptions, long numOfInterruptions) {
	double interruptionTotal = 0;

	if (interruptions != NULL) {
		int interruptCtr;
		for (interruptCtr = 0; interruptCtr < numOfInterruptions; interruptCtr++) {
			Interruption currentInterruption = *(interruptions + interruptCtr);
			
			if (currentInterruption.startTime > 0 && currentInterruption.endTime > 0) {
				interruptionTotal += difftime(currentInterruption.endTime, currentInterruption.startTime);
			}
		}		
	}

	return interruptionTotal;
}

double trlGetDeltaTime (Record trlLog, Interruption* interruptions, long numOfInterruptions, double* interruptionTime) {
	if (trlLog.startTime == 0 || trlLog.endTime == 0) {
		//cannot compute yet if a log for a phase hasn't started nor ended yet
		return 0;
	}

	double interruptionTotal = 0;

	//We count the delta time for the interruptions first.
	interruptionTotal = trlGetInterruptionTime(interruptions, numOfInterruptions);	
	
	//count the time log's delta time
	double logDelta = difftime(trlLog.endTime, trlLog.startTime);
	logDelta -= interruptionTotal;

	if (interruptionTime != NULL) {
		*(interruptionTime) = interruptionTotal;
	}

	return logDelta;
}

unsigned long trlGetInterruptionsForTimeLog (MYSQL* conn, Record* record, Interruption** interruptions) {
	if (record == NULL) {
		utilPrintError(NULL, "There is no associated record with this phase for this project");	
	}
	else {
		MYSQL_RES* resultSet;

		int logId = record->logId;

		char searchQuery[300];
		snprintf(searchQuery, 300, "SELECT * FROM %s WHERE %s = %d;",
			InterruptionTable.tableName,
			InterruptionTable.logId, logId
		);

		if (mysql_query(conn, searchQuery) != 0) {
			utilPrintError(NULL, "The query is invalid. Can't retrieve interruptions");
		}
		else {
			resultSet = mysql_store_result(conn);
			if (resultSet == NULL) {
				utilPrintError(conn, "The result set could not be stored");
			}
			else {
				const unsigned long numberOfRows = (unsigned long)mysql_num_rows(resultSet);
				Interruption* results = malloc(sizeof(Interruption) * numberOfRows);

				MYSQL_ROW row;
				unsigned int rowCtr = 0;
				unsigned int numOfFields = mysql_num_fields(resultSet);

				while ((row = mysql_fetch_row(resultSet)) != NULL) {
					*(results + rowCtr) = trlConvertInterruptionRow(row, numOfFields);
					rowCtr++;
				}

				mysql_free_result (resultSet);
				*interruptions = results;

				return numberOfRows;
			}
		}
	}

	return 0;
}

unsigned long trlGetInterruptionsForPhase (MYSQL* conn, Project project, Phase phase, Interruption** interruptions) {
	//retrieve the appropriate time recording log given the project and the phase
	Record* record = trlGetTimeLogForPhase(conn, project, phase);
	unsigned long numberOfRecords = trlGetInterruptionsForTimeLog(conn, record, interruptions);
	free(record);
	return numberOfRecords;
}

void imTrlDisplayTimeRecordingLog (MYSQL* conn, Project project) {
	printf("Time Recording Log\n\n");

	//TODO : retrieve user using user id

	//display program name
	printf("Project Name: PSP %d\n\n", project.pspId);

	//Lay the table out
	printf("Start (Date - Time)\t\tStop (Date - Time)\t\tInterruption Time\t\tDelta Time\t\tPhase\t\tComments\n");

	Phase* phases = NULL;
	unsigned long numOfPhases = priGetPhases(conn, &phases);
	int phaseCtr;

	if (phases != NULL) {
		for (phaseCtr = 0; phaseCtr < numOfPhases; phaseCtr++) {
			Phase currentPhase = *(phases + phaseCtr);

			//retrieve trl entry per phase
			Record* record = trlGetTimeLogForPhase(conn, project, currentPhase);
			if (record == NULL) {
				if (phaseCtr == 0) {
                    printf("No records yet.");
                }
                //end of the time log so far
				break;
			}
			else {
				//start time
				char* startTimeString = NULL;
				utilFormatTime(record->startTime, &startTimeString);
				printf("%s\t\t", startTimeString);
				//end time
				if (record->endTime == 0) {
                    printf("N/A\t\t");
                }
                else {
                    char* endTimeString = NULL;
                    utilFormatTime(record->endTime, &endTimeString);
                    printf("%s\t\t", endTimeString);
                }

				//get interruptions
				Interruption* interruptions = NULL;
				unsigned long numOfInterruptions = trlGetInterruptionsForTimeLog(conn, record, &interruptions);

				//get delta time
				double interruptionTime = 0;
				double deltaTime = trlGetDeltaTime(*record, interruptions, numOfInterruptions, &interruptionTime);

				//interruption time
				printf("%g minutes\t\t", interruptionTime / 60);

				//delta time
				printf("%g minutes\t\t", deltaTime / 60);

				//phase
				printf("%s\t\t", currentPhase.phaseName);

				//comments
				if (interruptions != NULL) {
					int interruptionCtr;
					for (interruptionCtr = 0; interruptionCtr < numOfInterruptions; interruptionCtr++) {
						Interruption currentInterruption = *(interruptions + interruptionCtr);
						if (currentInterruption.reason != NULL) {
							printf("%s", currentInterruption.reason);
						}
						else {
							printf("No reason stated");
						}

						printf(",");
					}

					free(interruptions);
				}

				free(record);
			}

			printf("\n\n");

		}
		free(phases);
	}
	else {
		utilPrintError(NULL, "Something may be wrong with the database.");
	}

	printf("Nothing follows.\n\n");
}

unsigned long trlRecordLogExecuteQuery (MYSQL* conn, char* query, Record** records) {
    if (query != NULL) {
        if (mysql_query(conn, query) != 0) {
            utilPrintError(conn, "Cannot execute query. Record log not retrieved.");
        }
        else {
            MYSQL_RES* results = mysql_store_result(conn);
            unsigned long numberOfRows = mysql_num_rows(results);
            Record* recordResults = malloc(sizeof(Record) * numberOfRows);
            
            MYSQL_ROW row;
            int rowCtr = 0;
            unsigned int numberOfFields = mysql_num_fields(results);
            
            while ((row = mysql_fetch_row(results)) != NULL) {
                *(recordResults + rowCtr) = trlConvertRecordRow(row, numberOfFields);
                rowCtr++;
            }
            
            mysql_free_result(results);
            *records = recordResults;
            return numberOfRows;
        }
    }
    else {
        utilPrintError(conn, "Cannot execute query. Record log not retrieved.");
    }
    
    return 0;
}

Phase* trlGetCurrentPhase (MYSQL* conn, Project project) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d AND %s <> 0 AND %s = 0;", RecordTable.tableName, RecordTable.userProjectId, project.userProjectId, RecordTable.startTime, RecordTable.endTime);
    
    Record* records = NULL;
    unsigned long numOfPhases = trlRecordLogExecuteQuery(conn, query, &records);
    
    if (numOfPhases == 0) {
        return NULL;
    }
    else {
        Phase* phase = malloc(sizeof(Phase));
        *phase = *(priGetPhaseById(conn, records->phaseId));
        free(records);
        return phase;
    }
}

int trlHasCurrentPhase (MYSQL* conn, Project project) {
    //convenience function calling get current phase that detects whether what it returns is NULL
    if (trlGetCurrentPhase(conn, project) == NULL) {
        return 0;
    }
    else {
        return 1;
    }
}

/**Gets the next step to the phase that was last finished; NULL is returned if the project is finished**/
Phase* trlGetUpcomingPhase (MYSQL* conn, Project project) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = (SELECT MAX(%s) FROM %s WHERE %s = %d) AND %s <> 0;",
             RecordTable.tableName, RecordTable.phaseId, RecordTable.phaseId, RecordTable.tableName, RecordTable.userProjectId, project.userProjectId, RecordTable.endTime);
    Record* record = NULL;
    unsigned long numOfResults = trlRecordLogExecuteQuery(conn, query, &record);
    
    if (numOfResults == 0) {
        //return planning phase (ID 1)
        return priGetPhaseById(conn, 1);
    }
    else if (record != NULL) {
        return priGetPhaseById(conn, record->phaseId + 1);
    }
    else {
        return NULL;
    }
}

void trlOnPostmortemFinish (MYSQL* conn, Project project, Phase phase) {
	if (strcmp(phase.phaseName, "Postmortem") == 0) {
		project.done = 1;
		pmSetProjectDoneWithId(conn, project.userId, project.pspId);
	}
}

/**DEFECT RECORDING LOG**/
Defect drlConvertDefectRow (MYSQL_ROW row, unsigned int numberOfFields) {
    int fieldCtr;
	Defect defect;
    
	for (fieldCtr = 0; fieldCtr < numberOfFields; fieldCtr++) {
		char* rowData = row[fieldCtr];
        
		if (fieldCtr == 0) {
			//defect ID
			defect.defectId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 1) {
			//user project ID
			defect.userProjectId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 2) {
			//inject phase
			defect.injectPhase = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 3) {
			//fix phase
			defect.fixPhase = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 4) {
			//start fix time
			defect.startFixTime = rowData != NULL ? atol(rowData) : -1;
		}
		else if (fieldCtr == 5) {
			//end fix time
			defect.endFixTime = rowData != NULL ? atol(rowData) : -1;
		}
		else if (fieldCtr == 6) {
			//description
			if (rowData != NULL) {
				defect.description = malloc(sizeof(char)*(strlen(rowData) + 1));
				strcpy(defect.description, rowData);
			}
			else {
				defect.description = NULL;
			}
		}
		else if (fieldCtr == 7) {
			//defect type ID
			defect.typeId = rowData != NULL ? atoi(rowData) : -1;
		}
		else {
			utilPrintError(NULL, "Unexpected column.");
		}
	}
    
	return defect;
}

DefectType drlConvertDefectTypeRow (MYSQL_ROW row, unsigned int numberOfFields) {
	int fieldCtr;
	DefectType defectType;
    
	for (fieldCtr = 0; fieldCtr < numberOfFields; fieldCtr++) {
		char* rowData = row[fieldCtr];
        
		if (fieldCtr == 0) {
			//type ID
			defectType.typeId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 1) {
			//type name
			if (rowData != NULL) {
				defectType.typeName = malloc(sizeof(char) * (strlen(rowData) + 1));
				strcpy(defectType.typeName, rowData);
			}
			else {
				defectType.typeName = NULL;
			}
		}
		else if (fieldCtr == 2) {
			//type desc
			if (rowData != NULL) {
				defectType.typeDesc = malloc(sizeof(char) * (strlen(rowData) + 1));
				strcpy(defectType.typeDesc, rowData);
			}
			else {
				defectType.typeDesc = NULL;
			}
		}
		else {
			utilPrintError(NULL, "Unexpected column.");
		}
	}
    
	return defectType;
}

DefectDependency drlConvertDefectDependencyRow (MYSQL_ROW row, unsigned int numberOfFields) {
	int fieldCtr;
	DefectDependency defectDependency;
    
	for (fieldCtr = 0; fieldCtr < numberOfFields; fieldCtr++) {
		char* rowData = row[fieldCtr];
		
		if (fieldCtr == 0) {
			//dependency id
			defectDependency.dependencyId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 1) {
			//defect created id
			defectDependency.defectCreatedId = rowData != NULL ? atoi(rowData) : -1;
		}
		else if (fieldCtr == 2) {
			//defect original id
			defectDependency.defectOriginalId = rowData != NULL ? atoi(rowData) : -1;
		}
		else {
			utilPrintError (NULL, "Unexpected column.");
		}
	}
    
	return defectDependency;
}

unsigned long drlDefectCategoryExecuteQuery (MYSQL* conn, char* query, DefectType** types) {
    if (query != NULL) {
        if (mysql_query(conn, query) != 0) {
            utilPrintError(conn, "The query is invalid. Couldn't execute query.");
        }
        else {
            MYSQL_RES* results = mysql_store_result(conn);
            if (results == NULL) {
                utilPrintError(conn, "Query failed. Couldn't execute query.");
            }
            else {
                const unsigned long numberOfRows = (unsigned long) mysql_num_rows(results);
                DefectType* typesResult = malloc(sizeof(DefectType) * numberOfRows);
                
                MYSQL_ROW row;
                unsigned int rowCtr = 0;
                unsigned int numOfFields = mysql_num_fields(results);
                while ((row = mysql_fetch_row(results)) != NULL) {
                    *(typesResult + rowCtr) = drlConvertDefectTypeRow(row, numOfFields);
                    rowCtr++;
                }
                
                mysql_free_result(results);
                *types = typesResult;
                
                return numberOfRows;
            }
        }
    }
    
    return 0;
}

unsigned long drlDefectExecuteQuery (MYSQL* conn, char* query, Defect** defects) {
    if (query != NULL) {
        if (mysql_query(conn, query)!= 0) {
            utilPrintError(conn, "The query is invalid. Couldn't execute query.");
        }
        else {
            MYSQL_RES* results = mysql_store_result(conn);
            if (results == NULL) {
                utilPrintError(conn, "Query failed. Couldn't execute query.");
            }
            else {
                const unsigned long numberOfRows = (unsigned long)mysql_num_rows(results);
                Defect* defectsResult = malloc(sizeof(Defect) * numberOfRows);
                
                MYSQL_ROW row;
                unsigned int rowCtr = 0;
                unsigned int numOfFields = mysql_num_fields(results);
                while ((row = mysql_fetch_row(results)) != NULL) {
                    *(defectsResult + rowCtr) = drlConvertDefectRow(row, numOfFields);
                    rowCtr++;
                }
                
                mysql_free_result(results);
                *defects = defectsResult;
                
                return numberOfRows;
            }
        }
    }
    
    return 0;
}

FixPause drlConvertFixPauseRow (MYSQL_ROW row, unsigned int numberOfFields) {
    unsigned int fieldCtr;
    FixPause pauseInstance;
    
    for (fieldCtr = 0; fieldCtr < numberOfFields; fieldCtr++) {
        char* rowData = row[fieldCtr];
        if (fieldCtr == 0) {
            //pause id
            pauseInstance.pauseId = rowData != NULL ? atoi(rowData) : -1;
        }
        else if (fieldCtr == 1) {
            //defect id
            pauseInstance.defectId = rowData != NULL ? atoi(rowData) : -1;
        }
        else if (fieldCtr == 2) {
            //start time
            pauseInstance.startTime = rowData != NULL ? atol(rowData) : 0;
        }
        else if (fieldCtr == 3) {
            //end time
            pauseInstance.endTime = rowData != NULL ? atol(rowData) : 0;
        }
        else {
            //unexpected column
            utilPrintError(NULL, "Unexpected column found in fix pause");
        }
    }
    
    return pauseInstance;
}

unsigned long drlFixPauseExecuteQuery (MYSQL* conn, char* query, FixPause** pauses) {
    if (query != NULL) {
        if (mysql_query(conn, query) != 0) {
            utilPrintError(conn, "Query execution failed for defect recording log.");
        }
        else {
            MYSQL_RES* results = mysql_store_result(conn);
            if (results == NULL) {
                utilPrintError(conn, "Query failed.");
            }
            else {
                const unsigned long numberOfRows = mysql_num_rows(results);
                FixPause* pauseResults = malloc(sizeof(FixPause) * numberOfRows);
                
                unsigned int rowCtr = 0;
                unsigned int numOfFields = mysql_num_fields(results);
                MYSQL_ROW row;
                
                while ((row = mysql_fetch_row(results)) != NULL) {
                    *(pauseResults + rowCtr) = drlConvertFixPauseRow(row, numOfFields);
                    rowCtr++;
                }
                
                mysql_free_result(results);
                *pauses = pauseResults;
                return numberOfRows;
            }
            
        }
    }
    
    return 0;
}

unsigned long drlGetFixPausesForDefect (MYSQL* conn, Defect defect, FixPause** pauses) {
    //check if defect is being fixed (has start fix time > 0)
    if (defect.startFixTime == 0) {
        //the defect is not being fixed right now
        return 0;
    }
    else {
        char query[300];
        snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;", FixPauseTable.tableName, FixPauseTable.defectId, defect.defectId);
        return drlFixPauseExecuteQuery(conn, query, pauses);
    }
}

unsigned long drlGetDefectCategories (MYSQL* conn, DefectType** defects) {
    //"SELECT * FROM %s", DefectType.tableName
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s;", DefectTypeTable.tableName);
    return drlDefectCategoryExecuteQuery(conn, query, defects);
}

void drlInsertDefect (MYSQL* conn, Defect defect) {
    //insert to database
    if (defect.description != NULL) {
        utilEscapeStringValue(&defect.description);
    }
    
    char insert[300];
    snprintf(insert, 300, "INSERT INTO %s (%s, %s, %s, %s) VALUES (%d, %d, '%s', %d);", DefectTable.tableName,
        DefectTable.userProjectId, DefectTable.injectPhase, DefectTable.description, DefectTable.typeId,
        defect.userProjectId, defect.injectPhase, defect.description, defect.typeId);
    
    if (mysql_query(conn, insert) != 0) {
        utilPrintError(conn, "Insert statement failed.");
    }
}

void drlUpdateDefect (MYSQL* conn, Defect defect) {
    char update[300];
    snprintf(update, 300, "UPDATE %s SET %s = %d, %s = %ld, %s = %ld WHERE %s = %d;", DefectTable.tableName,
        DefectTable.fixPhase, defect.fixPhase,
        DefectTable.startFixTime, defect.startFixTime,
        DefectTable.endFixTime, defect.endFixTime,
        DefectTable.defectId, defect.defectId);
    
    if (mysql_query(conn, update) != 0) {
        utilPrintError(conn, "Update failed to execute.");
    }
}

void drlInjectDefect (MYSQL* conn, Project project, Phase phase, char* description, DefectType type) {
    //package for database
    Defect* defect = malloc(sizeof(Defect));
    
    defect->userProjectId = project.userProjectId;
    defect->injectPhase = phase.phaseId;
    if (description != NULL) {
        defect->description = malloc(sizeof(char) * (strlen(description) + 1));
        strcpy(defect->description, description);
    }
    else {
        defect->description = NULL;
    }
    defect->typeId = type.typeId;
    
    drlInsertDefect(conn, *defect);
    
    free(defect);
}

int drlIsDefectFixed (MYSQL* conn, Defect defect) {
    if (defect.endFixTime == 0) {
        return 0;
    }
    else {
        return 1;
    }
}

long drlGetDefectFixDeltaTime (MYSQL* conn, Defect defect) {
    if (defect.startFixTime == 0 || defect.endFixTime == 0) {
		return 0;
	}
    
	long deltaTime = difftime(defect.endFixTime, defect.startFixTime);
    
	//get defect fix pauses
	FixPause* pauses = NULL;
	unsigned long numberOfPauses = drlGetFixPausesForDefect (conn, defect, &pauses);
	
	if (numberOfPauses == 0) {
		return deltaTime;
	}
	else {
		int pauseCtr;
		for (pauseCtr = 0; pauseCtr < numberOfPauses; pauseCtr++) {
			FixPause curPause = *(pauses + pauseCtr);
			if (curPause.endTime == 0) {
				//a pause still persists but get the delta time thus far
				deltaTime -= difftime(curPause.startTime, time(NULL));
			}
			else {
				long pauseTime = difftime(curPause.startTime, curPause.endTime);
				deltaTime -= pauseTime;
			}
		}
        
		return deltaTime;
	}
}

void drlInsertFixPause (MYSQL* conn, FixPause pause) {
    char insert[300];
    
    snprintf(insert, 300, "INSERT INTO %s (%s, %s, %s) VALUES (%d, %ld, %ld);", FixPauseTable.tableName,
        FixPauseTable.defectId, FixPauseTable.startTime, FixPauseTable.endTime, pause.defectId, pause.startTime, pause.endTime);
    
    if (mysql_query(conn, insert) != 0) {
        utilPrintError(conn, "Insert statement failed for fix pause table");
    }
}

void drlUpdateFixPause (MYSQL* conn, FixPause pause) {
    char update[300];
    
    snprintf(update, 300, "UPDATE %s SET %s = %ld WHERE %s = %d;", FixPauseTable.tableName, FixPauseTable.endTime, pause.endTime, FixPauseTable.defectId, pause.defectId);
    
    if (mysql_query(conn, update) != 0) {
        utilPrintError(conn, "Update statement failed for fix pause table");
    }
}

void drlPauseFixDefect (MYSQL* conn, Defect defect) {
    //insert pause fix record
    FixPause* pause = malloc(sizeof(FixPause));
    pause->defectId = defect.defectId;
	pause->startTime = time(NULL);
	pause->endTime = 0;
	drlInsertFixPause(conn, *pause);
	free(pause);
}

FixPause* drlGetUnendedFixPause (MYSQL* conn, Defect defect) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d AND %s = 0;", FixPauseTable.tableName, FixPauseTable.defectId, defect.defectId, FixPauseTable.endTime);
    
    FixPause* pause = NULL;
    
    unsigned long numOfResults = drlFixPauseExecuteQuery(conn, query, &pause);
    
    if (numOfResults == 0) {
        return NULL;
    }
    else {
        return pause;
    }
}

void drlUnpauseFixDefect (MYSQL* conn, Defect defect) {
    //update pause fix record end time
    FixPause* pause = drlGetUnendedFixPause(conn, defect);
    
    if (pause != NULL) {
        pause->endTime = time(NULL);
        drlUpdateFixPause(conn, *pause);
    }
}

void drlFinishFixDefect (MYSQL* conn, Phase phase, Defect defect) {
	//unpause any pauses for this defect
	drlUnpauseFixDefect(conn, defect);
    
    //update defect record
	defect.endFixTime = time(NULL);
	defect.fixPhase = phase.phaseId;
	drlUpdateDefect(conn, defect);
}

DefectType* drlGetDefectTypeById (MYSQL* conn, int typeId) {
    char query[300];
    
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;", DefectTypeTable.tableName,
            DefectTypeTable.typeId, typeId);
    
    DefectType* typeRes = NULL;
    
    unsigned long numOfTypes = drlDefectCategoryExecuteQuery(conn, query, &typeRes);
    
    if (numOfTypes == 0) {
        return NULL;
    }
    else {
        return typeRes;
    }
}

unsigned long drlGetDefectsForProject (MYSQL* conn, Project project, Defect** defects) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;", DefectTable.tableName, DefectTable.userProjectId, project.userProjectId);
    
    return drlDefectExecuteQuery(conn, query, defects);
}

unsigned long drlGetDefectsInjectedAtProjectPhase (MYSQL* conn, Project project, Phase phase, Defect** defects) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d AND %s = %d;", DefectTable.tableName, DefectTable.userProjectId, project.userProjectId, DefectTable.injectPhase, phase.phaseId);
    return drlDefectExecuteQuery(conn, query, defects);
}

unsigned long drlGetDefectsFixedAtProjectPhase (MYSQL*conn, Project project, Phase phase, Defect** defects) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d AND %s = %d;", DefectTable.tableName, DefectTable.userProjectId, project.userProjectId, DefectTable.fixPhase, phase.phaseId);
    return drlDefectExecuteQuery(conn, query, defects);
}

int drlGetDefectInformationString (MYSQL* conn, Defect defect, char** defectStr) {
    int stringSize = 500;
    int stringActualLength = 0;
    char* defectString = malloc(sizeof(char) * stringSize);
    snprintf(defectString, 500, "Defect ID: %d\n", defect.defectId);
    stringActualLength += strlen(defectString);
    
    int stringPartCtr = 1;
    int numOfParts = 6;
    
    while (stringPartCtr != numOfParts + 1) {
        if (stringActualLength >= stringSize / 2) {
            stringSize *= 2;
            char* tempAlloc = realloc(defectString, stringSize);
            if (tempAlloc == NULL) {
                utilPrintError(NULL, "Can't reallocate memory... failed.");
                *defectStr = defectString;
                return stringActualLength;
            }
            else {
                defectString = tempAlloc;
            }
        }
        
        if (stringPartCtr == 1) {
            //change to get project name by id
            stringActualLength += snprintf(defectString + stringActualLength, stringSize, "From project: %d\n", defect.userProjectId);
        }
        
        //change to get phase by id
        else if (stringPartCtr == 2 && defect.injectPhase > 0) {
            stringActualLength += snprintf(defectString + stringActualLength, stringSize, "Inject phase: %s\n", priGetPhaseById(conn, defect.injectPhase)->phaseName);
        }
        
        //change to get phase by id, if available
        else if (stringPartCtr == 3 && defect.fixPhase > 0) {
            stringActualLength += snprintf(defectString + stringActualLength, stringSize, "Fix phase: %s\n", priGetPhaseById(conn, defect.fixPhase)->phaseName);
        }
        
        else if (stringPartCtr == 4 && drlIsDefectFixed(conn, defect) == 1) {
            stringActualLength += snprintf(defectString + stringActualLength, stringSize, "Fix time: %ld\n", drlGetDefectFixDeltaTime(conn, defect) / 60);
        }
        
        else if (stringPartCtr == 5 && defect.typeId > 0) {
            stringActualLength += snprintf(defectString + stringActualLength, stringSize, "Defect type: %s\n", drlGetDefectTypeById(conn, defect.typeId)->typeName);
        }
        
        else if (stringPartCtr == 6 && defect.description != NULL) {
            if (stringSize - stringActualLength < strlen(defect.description)) {
                stringSize = 1 + stringActualLength + (unsigned int)strlen(defect.description);
                defectString = realloc(defectString, stringSize);
            }
            stringActualLength += snprintf(defectString + stringActualLength, stringSize, "Description: %s\n", defect.description);
        }
        
        stringPartCtr++;
    }
    
    if (stringSize - 1 != stringActualLength) {
        defectString = realloc(defectString, stringActualLength + 1);
    }
    
    *defectStr = defectString;
    return stringActualLength;
}

void drlDisplayDefectInformation (MYSQL* conn, Defect defect) {
    char* strToPrint = NULL;
    drlGetDefectInformationString(conn, defect, &strToPrint);
    printf("%s", strToPrint);
}

void drlDisplayDefectLog (MYSQL* conn, Project project) {
    Defect *defectsForProject = NULL;
    
    unsigned long numOfDefects = drlGetDefectsForProject(conn, project, &defectsForProject);
    
    if (numOfDefects == 0) {
        printf("No defects recorded.\n");
    }
    else {
        int defectsCtr;
        for (defectsCtr = 0; defectsCtr < numOfDefects; defectsCtr++) {
            Defect curDefect = *(defectsForProject + defectsCtr);
            drlDisplayDefectInformation(conn, curDefect);
            printf("\n");
        }
    }
}

unsigned long drlGetUnfixedDefects (MYSQL* conn, Project project, Defect** defects) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d AND %s = %d;", DefectTable.tableName, DefectTable.userProjectId, project.userProjectId, DefectTable.endFixTime, 0);
    
    return drlDefectExecuteQuery(conn, query, defects);
}

int drlIsFixPauseEnded (MYSQL* conn, Defect defect) {
    FixPause* pause = drlGetUnendedFixPause(conn, defect);
    if (pause == NULL) {
        return 1;
    }
    else {
        return 0;
    }
}

unsigned long drlGetDefectsBeingFixed (MYSQL* conn, Project project, Defect** defects) {
    char query[300];
    
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d AND %s <> %d AND %s = %d;",
             DefectTable.tableName, DefectTable.userProjectId, project.userProjectId, DefectTable.startFixTime, 0, DefectTable.endFixTime, 0);
    
    Defect* unfixedDefects = NULL;
    unsigned long numberOfUnfixedDefects = drlDefectExecuteQuery(conn, query, &unfixedDefects);
    
    if (numberOfUnfixedDefects == 0) {
        return 0;
    }
    
    //check if the defects being fixed (since they have start time != 0) are paused right now
    Defect* defectFixInProgress = NULL;
    unsigned long numberOfFixesInProgress = 0;
    int unfixedCtr = 0;
    for (; unfixedCtr < numberOfUnfixedDefects; unfixedCtr++) {
        Defect curDefect = *(unfixedDefects + unfixedCtr);
        if (drlIsFixPauseEnded(conn, curDefect)) {
            //a defect that is being fixed is not paused
            numberOfFixesInProgress++;
            if (defectFixInProgress == NULL) {
                defectFixInProgress = malloc(sizeof(Defect));
            }
            else {
                Defect* tempFixAlloc = realloc(defectFixInProgress, sizeof(Defect) * numberOfFixesInProgress);
                
                if (tempFixAlloc == NULL) {
                    utilPrintError(NULL, "Can't reallocate list of defects being fixed.");
                }
                else {
                    defectFixInProgress = tempFixAlloc;
                }
            }
            *(defectFixInProgress + (numberOfFixesInProgress - 1)) = curDefect;
        }
    }
    
    *defects = defectFixInProgress;
    
    return numberOfFixesInProgress;
}

void drlStartFixDefect (MYSQL* conn, Phase phase, Defect defect) {
    //check if there are other defects with a fix in progress
    Project project;
	project.userProjectId = defect.userProjectId;
    
	Defect* defects = NULL;
	unsigned long numberOfDefectsBeingFixed = drlGetDefectsBeingFixed(conn, project, &defects);
	free(defects);
    
	if (numberOfDefectsBeingFixed == 0) {
		//update defect record
		defect.startFixTime = time(NULL);
		defect.endFixTime = 0;
		defect.fixPhase = phase.phaseId;
		drlUpdateDefect(conn, defect);
	}
}

Defect* drlGetDefectById (MYSQL* conn, int defectId) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;", DefectTable.tableName, DefectTable.defectId, defectId);
    
    Defect* defectResults = NULL;
    
    unsigned long numberOfDefects = drlDefectExecuteQuery(conn, query, &defectResults);
    
    if (numberOfDefects == 0) {
        return NULL;
    }
    else {
        //there can only be one...
        return defectResults;
    }
}

unsigned long drlDefectDependencyExecuteQuery (MYSQL* conn, char* query, DefectDependency** dependencies) {
    if (query != NULL) {
        if (mysql_query(conn, query) != 0) {
            utilPrintError(conn, "Cannot execute query on defect dependency table.");
        }
        else {
            MYSQL_RES* results = mysql_store_result(conn);
            
            unsigned long numOfRows = mysql_num_rows(results);
            DefectDependency* dependencyResults = malloc(sizeof(DefectDependency) * numOfRows);
            
            unsigned int numOfFields = mysql_num_fields(results);
            MYSQL_ROW row;
            unsigned int rowCtr = 0;
            
            while ((row = mysql_fetch_row(results)) != NULL) {
                *(dependencyResults + rowCtr) = drlConvertDefectDependencyRow(row, numOfFields);
                rowCtr++;
            }
            mysql_free_result(results);
            *dependencies = dependencyResults;
            return numOfRows;
        }
    }
    else {
        utilPrintError(conn, "Defect dependency table could not be queried.");
    }
    
    return 0;
}

Defect* drlGetDefectDependency (MYSQL* conn, Defect originalDefect) {
    char query[300];
    snprintf(query, 300, "SELECT * FROM %s WHERE %s = %d;", DefectDependencyTable.tableName, DefectDependencyTable.defectOriginalId, originalDefect.defectId);
    DefectDependency* dependency = NULL;    
    //there can only be one...
    unsigned long numOfDependencies = drlDefectDependencyExecuteQuery(conn, query, &dependency);
    
    if (numOfDependencies == 0) {
        return NULL;
    }
    else {
        return drlGetDefectById(conn, dependency->defectCreatedId);
    }
}

void drlInsertDefectDependency (MYSQL* conn, DefectDependency defectDependency) {
    char insert[300];
    snprintf(insert, 300, "INSERT INTO %s (%s, %s) VALUES (%d, %d);", DefectDependencyTable.tableName, DefectDependencyTable.defectCreatedId, DefectDependencyTable.defectOriginalId, defectDependency.defectCreatedId, defectDependency.defectOriginalId);
    
    if (mysql_query(conn, insert) != 0) {
        utilPrintError(conn, "Could not execute insert statement for defect dependency.");
    }
}

Defect* drlSelectDefectDependency (MYSQL* conn, Project project) {
    //list all the defects right now and ask the user which he/she wants
    Defect* defects = NULL;
    unsigned long numOfDefects = drlGetDefectsForProject(conn, project, &defects);
    
    char* choices[numOfDefects + 1];
    int typeCtr;
    for (typeCtr = 0; typeCtr < numOfDefects; typeCtr++) {
        Defect curDefect = *(defects + typeCtr);
        choices[typeCtr] = malloc(sizeof(char) * (strlen(curDefect.description) + 1));
        strcpy(choices[typeCtr], curDefect.description);
    }
    
    char* backString = "Back";
    choices[numOfDefects] = malloc(sizeof(char) * (strlen(backString) + 1));
    strcpy(choices[numOfDefects], backString);
    
    int typeChoice = imSelectFromChoices(choices, (unsigned int)numOfDefects + 1, "Select a defect that the current defect is dependent on");
    
    if (typeChoice == numOfDefects + 1) {
        return NULL;
    }
    else {
        Defect* defectToRet = malloc(sizeof(Defect));
        *defectToRet = *(defects + (typeChoice - 1));
        free(defects);
        return defectToRet;
    }
}

/**TEST REPORT TEMPLATE**/

int trtCountDetails(MYSQL* conn, string300 query){
	MYSQL_RES* result;
	MYSQL_ROW row;
	int count = 0;
	string300 countQuery;
	
	sprintf(countQuery, "%s", query);
	if(mysql_query(conn, countQuery) != 0){
		printf("Error: Count Query Failed.");
	}
	else{
		result = mysql_store_result(conn);
		if(result == NULL){
			utilPrintError(conn, "No count produced.");
		}
		else{
			while((row = mysql_fetch_row(result))){
				count = atoi(row[0]);
			}
            mysql_free_result(result);
		}
	}
	return count;
}

void trtConcatString(string300 query, int detail, int userProjectId, int testNum){
	string100 query1;
	string100 query2;
	string100 query3;
	char projId[11];
	char testNo[11];
	
	sprintf(projId, "%d", userProjectId);
	sprintf(testNo, "%d", testNum);
	
	strcpy(query2, "' AND test_number = '");
	strcpy(query3, "';");
	
	switch(detail){
		case 1:
			strcpy(query1, "SELECT test_objective FROM testreport WHERE user_project_id='");
			break;
		case 2:
			strcpy(query1, "SELECT test_description FROM testreport WHERE user_project_id='");
			break;
		case 3:
			strcpy(query1, "SELECT expected_results FROM testreport WHERE user_project_id='");
			break;
		case 4:
			strcpy(query1, "SELECT actual_results FROM testreport WHERE user_project_id='");
			break;
		case 5:
			strcpy(query1, "SELECT * FROM testreport WHERE user_project_id='");
			break;
		case 6:
			strcpy(query1, "SELECT COUNT(*) FROM testreport WHERE user_project_id='");
			break;
		case 7:
			strcpy(query1, "SELECT COUNT(*) FROM testreport WHERE actual_results IS NULL AND user_project_id='");
			break;
	}
	
	strcat(query1, projId);
	if(detail != 5 && detail != 6){
		strcat(query1, query2);
		strcat(query1, testNo);
	}
	strcat(query1, query3);
	strcpy(query, query1);
}

void trtSelectDetails(MYSQL* conn, string300 query){
	MYSQL_RES* result;
	MYSQL_ROW row;
	int num_fields;
	int i;
	string300 selectQuery;
	
	sprintf(selectQuery, "%s", query);
	if(mysql_query(conn, selectQuery) != 0){
		printf("Query failed \n");
	}
	else {
		result = mysql_store_result(conn);
		if(result == NULL){
			utilPrintError(conn, "No matching results found.");
		}
		else {
			num_fields = mysql_num_fields(result);
			while((row = mysql_fetch_row(result))){
				for(i = 0; i < num_fields; i++){
					printf("%s ", row[i] ? row[i] : "NULL");
				}
				printf("\n");
			}
            mysql_free_result(result);
		}
	}
}

int trtUpdateDetails(MYSQL* conn, string500 actualResults, int testNum){
	string300 updateQuery;
	
	sprintf(updateQuery, "UPDATE %s SET %s='%s' WHERE %s = %d;",
            TrtTable.tableName,
            TrtTable.actualResults, actualResults,
            TrtTable.testNumber, testNum);
	if(mysql_query(conn, updateQuery) != 0){
		return 0;
	}
	else {
		return 1;
	}
}

void trtInputActualResults(MYSQL* conn, int userProjectId){
	string300 querySelect;
	strcpy(querySelect, "SELECT test_number, test_description FROM testreport WHERE actual_results IS NULL;");
	int choice;
	int trans;
	string500 actualResults;
	int entryCount;
	string300 queryCount;
	char ans, dump;
	
	do{
		CLEARSCR;
		printf("Add Actual Test Results\n\n");
		trtSelectDetails(conn, querySelect);
        
		printf("\n\nChoose a test number: ");
		scanf("%d%c", &choice, &dump);
		
		trtConcatString(queryCount, 7, userProjectId, choice);
		entryCount = trtCountDetails(conn, queryCount);
		
		if(entryCount == 0){
			printf("Invalid input, choose from test numbers listed above...");
			getch();
		}
	} while(entryCount == 0);
	
	CLEARSCR;
	imTrtDisplayReport(conn, userProjectId, choice);
    utilFlush();
	printf("\n\nActual Results: ");
	gets(actualResults);
	
	printf("Save the details (Y/N)?");
	scanf("%c%c", &ans, &dump);
	if(ans == 'y' || ans == 'Y'){
		trans = trtUpdateDetails(conn, actualResults, choice);
		if(!trans){
			printf("\nError: Transaction Failed; Check database status...\n");
		}
		else{
			printf("\nActual Results Updated!");
		}
        utilFlush();
		getch();
	}
}

int trtInsertDetails(MYSQL* conn, TestReport testReport){
	string1000 insertQuery;
	
	sprintf(insertQuery, "INSERT INTO %s(%s, %s, %s, %s, %s, %s) VALUES (%d, (SELECT %s FROM project WHERE %s = %d), '%s', '%s', '%s', '%s');",
            TrtTable.tableName,
            TrtTable.testNumber, TrtTable.userProjectId, TrtTable.testObjective,
            TrtTable.testConditions, TrtTable.expectedResults, TrtTable.testDescription,
            testReport.testNumber, TrtTable.userProjectId, TrtTable.userProjectId,
            testReport.userProjectId, testReport.testObjective, testReport.testConditions,
            testReport.expectedResults, testReport.testDescription);
	
	if(mysql_query(conn, insertQuery) != 0){
		return 0;
	}
	else {
		return 1;
	}
}

/**PROCESS IMPROVEMENT PLAN**/
unsigned long pipGetPlanIdFromProject (MYSQL* conn, Project project) {
    char query[500];
    snprintf(query, 500, "SELECT * FROM piplan WHERE user_project_id = %d", project.userProjectId);
    
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "GET PLAN ID FROM PROJECT: The query failed.");
    }
    else {
        MYSQL_RES* result = mysql_store_result(conn);
        MYSQL_ROW row;
        unsigned long numOfRows = mysql_num_rows(result);
        
        if (numOfRows != 0) {
            row = mysql_fetch_row(result);
            int pipPlanId = atoi(row[0]);
            return pipPlanId;
        }
        
        mysql_free_result(result);
    }
    
    return -1;
}

int pipCountDetails(MYSQL* conn, string300 query){
	MYSQL_RES* result;
	MYSQL_ROW row;
	int count = 0;
	string300 countQuery;
	
	sprintf(countQuery, "%s", query);
	if(mysql_query(conn, countQuery) != 0){
		printf("Error: Count Query Failed.");
	}
	else{
		result = mysql_store_result(conn);
		if(result == NULL){
			utilPrintError(conn, "No count produced.");
		}
		else{
			while((row = mysql_fetch_row(result))){
				count = atoi(row[0]);
			}
            mysql_free_result(result);
		}
	}
	
	return count;
}

int pipUpdateDetails(MYSQL* conn, string800 notes, int piplanId){
	string1000 updateQuery;
	
	sprintf(updateQuery, "UPDATE %s SET %s = '%s' WHERE %s = %d;",
            ProcImpPlanTable.tableName,
            ProcImpPlanTable.notes, notes,
            ProcImpPlanTable.piplanId, piplanId);
	if(mysql_query(conn, updateQuery) != 0){
		return 0;
	}
	else {
		return 1;
	}
}

void pipSelectDetails(MYSQL* conn, int details, int piplanId){
	string300 query;
	switch(details){
		case 1: strcpy(query, "SELECT problem_id, description FROM pipproblem WHERE piplan_id = ");
			break;
		case 2: strcpy(query, "SELECT proposal_id, description FROM pipproposal WHERE piplan_id = ");
			break;
		case 3: strcpy(query, "SELECT notes FROM piplan WHERE piplan_id = ");
			break;
	}
	char pipId[11];
	sprintf(pipId, "%d", piplanId);
	strcat(query, pipId);
	strcat(query, ";");
	
	MYSQL_RES* result;
	MYSQL_ROW row;
	int num_fields;
	int i;
	string300 selectQuery;
	
	sprintf(selectQuery, "%s", query);
	if(mysql_query(conn, selectQuery) != 0){
		printf("Query failed \n");
	}
	else {
		result = mysql_store_result(conn);
		if(result == NULL){
			utilPrintError(conn, "No matching results found.");
		}
		else {
			num_fields = mysql_num_fields(result);
			while((row = mysql_fetch_row(result))){
				for(i = 0; i < num_fields; i++){
					printf("%s           ", row[i] ? row[i] : "NULL");
				}
				printf("\n");
			}
            mysql_free_result(result);
		}
	}
}

int pipInsertDetails(MYSQL* conn, int detail, int id, int piplanId,
                     string300 description){
	string1000 query;
	switch(detail){
		case 1: sprintf(query, "INSERT INTO %s VALUES (%d, (SELECT %s FROM %s WHERE %s = %d), '%s');",
                        ProcImpProbTable.tableName,
                        id, ProcImpProbTable.piplanId, ProcImpPlanTable.tableName,
                        ProcImpProbTable.piplanId, piplanId, description);
			break;
		case 2: sprintf(query, "INSERT INTO %s VALUES (%d, (SELECT %s FROM %s WHERE %s = %d), '%s');",
                        ProcImpPropTable.tableName,
                        id, ProcImpPropTable.piplanId, ProcImpPlanTable.tableName,
                        ProcImpPropTable.piplanId, piplanId, description);
			break;
		case 3: sprintf(query, "INSERT INTO %s(%s, %s) VALUES (%d, (SELECT %s FROM project WHERE %s = %d));",
                        ProcImpPlanTable.tableName,
                        ProcImpPlanTable.piplanId, ProcImpPlanTable.userProjectId, piplanId,
                        ProcImpPlanTable.userProjectId, ProcImpPlanTable.userProjectId, id);
			break;
	}
	if(mysql_query(conn, query) != 0){
		return 0;
	}
	else {
		return 1;
	}
}

/**USER SECTION MODULE**/

void usmLogin(MYSQL* conn){
    //char usmInput = ' ';
    //int usmLength = 0;
    UserAccount usmLoginDetails;
    CLEARSCR;
    printf("Log In\n\n");
    utilFlush();
    printf("User name: ");
    scanf("%s", usmLoginDetails.usmUserName);
    printf("Password: ");
    usmInputPassword(usmLoginDetails.usmPassword);
    //printf("\n%s,%s\n", usmLoginDetails.usmUserName, usmLoginDetails.usmPassword);
    if(usmVerifyAccount(conn, usmLoginDetails)){
        printf("\nAccess Granted\n\n");
        imPmViewProjects(conn, usmLoginDetails.usmUserName);
    }else{
        printf("Access Error. Please press any key to go back...");
        utilFlush();
        getch();
        return;
    }
}

void usmSignup(MYSQL* conn){
    //char usmInput = ' ';
    //int usmLength = 0;
    string20 usmCreatedPassword;
    string20 usmConfirmedPassword;
    UserAccount usmLoginDetails;
    CLEARSCR;
    printf("Sign Up");
    printf("\nUser name: ");
    scanf("%s", usmLoginDetails.usmUserName);
    do{
        printf("\nCreate Password: ");
        usmInputPassword(usmCreatedPassword);
        printf("\nConfirm Password: ");
        usmInputPassword(usmConfirmedPassword);
        if(strcmp(usmCreatedPassword, usmConfirmedPassword))
            printf("\nPassword do not match");
    }while(strcmp(usmCreatedPassword, usmConfirmedPassword));
    strcpy(usmLoginDetails.usmPassword, usmCreatedPassword);
    string3 usmSectionTemp;
    
    printf("\n\nSection: ");
    usmChooseSections(conn, usmSectionTemp);
    strcpy(usmLoginDetails.usmSection, usmSectionTemp);
    if(usmDataExists(conn, usmLoginDetails.usmUserName, "user_name", "user")){
        printf("\nUser name already exists. Choose a new one...");
        utilFlush();
        getch();
        return;
    }else{
        usmInsertUser(conn,usmLoginDetails);
        printf("Sign-up success! Press any key to continue...");
        utilFlush();
        getch();
        imPmViewProjects(conn, usmLoginDetails.usmUserName);
    }
}

void usmInputPassword(string20 usmPassword){
    strcpy(usmPassword," ");
    char usmInput = ' ';
    int usmLength = 0;
//    int enterBuffer = 1;
    
    utilFlush();
    
    do
    {
        usmInput = getch();
        
//        if (utilIsEnter(usmInput) && enterBuffer) {
//            usmInput = getch();
//        }
        
//        enterBuffer = 0;
        
        if(!utilIsEnter(usmInput) && !utilIsBackspace(usmInput) && usmLength < 20 && usmLength >= 0)
        {
            usmPassword[usmLength] = usmInput;
            printf("*");
            usmLength++;
        }
        else if(utilIsBackspace(usmInput) && usmLength > 0)
        {
            printf("\b");
            printf(" ");
            printf("\b");
            usmPassword[usmLength] = '\0';
            usmLength--;
        }
    } while(!utilIsEnter(usmInput));
    
    usmPassword[usmLength] = '\0';
    
}

void usmInsertUser(MYSQL* conn, UserAccount usmAccount){
    string500 usmQuery;
    sprintf(usmQuery, "insert into user(user_name, password, section_id) values ('%s','%s',(select section_id from section where section_name = '%s'));",
            usmAccount.usmUserName,usmAccount.usmPassword,usmAccount.usmSection);
    mysql_query(conn,usmQuery);
    pmGenerateProjects(conn, usmAccount.usmUserName);
}

int usmDataExists(MYSQL* conn, char * basis, char * basis_column, char * basis_table){
    int usmExisted = 0;
    MYSQL_RES *res;
    MYSQL_ROW row;
    string500 usmQuery;
    sprintf(usmQuery, "select %s from %s;", basis_column, basis_table);
    mysql_query(conn,usmQuery);
    res = mysql_use_result(conn);
    
    while((row = mysql_fetch_row(res)) != NULL){
        if(!strcmp(row[0],basis)){
            usmExisted = 1;
        }
    }
    
    mysql_free_result(res);
    return usmExisted;
}

void usmChooseSections(MYSQL* conn, string3 usmSectionTemp){
    //int usmLogInValid = 0;
    MYSQL_RES *res;
    MYSQL_ROW row;
    string3 usmSections[100];
    string500 usmQuery;
    sprintf(usmQuery, "select section_name from section;");
    mysql_query(conn,usmQuery);
    res = mysql_use_result(conn);
    int i = 0;
    int usmInput;
    while((row = mysql_fetch_row(res)) != NULL){
        i++;
        printf("\n[%d] %s", i, row[0]);
        strcpy(usmSections[i-1],row[0]);
    }
    do{
        printf("\n\nEnter section: ");
        scanf("%d",&usmInput);
        if(usmInput<1 || usmInput>i){
            printf("Sorry, invalid index...");
            getch();
        }
    }while(usmInput<1 || usmInput>i);
    strcpy(usmSectionTemp,usmSections[usmInput-1]);
    mysql_free_result(res);
}

int usmVerifyAccount(MYSQL* conn, UserAccount usmAccount){
    int usmLogInValid = 0;
    MYSQL_RES *res;
    MYSQL_ROW row;
    
    string500 usmQuery;
    sprintf(usmQuery, "select * from user where user_name = '%s' and password = '%s';",
            usmAccount.usmUserName,usmAccount.usmPassword);
    mysql_query(conn,usmQuery);
    res = mysql_use_result(conn);
    
    while((row = mysql_fetch_row(res)) != NULL){
        usmLogInValid = 1;
    }
    mysql_free_result(res);
    return usmLogInValid;
}

void usmAddSection(MYSQL *conn){
    Section usmSectionDetails;
    CLEARSCR;
    printf("Add Section\n");
    printf("Section Name (3 chars. max):");
    scanf("%3s", usmSectionDetails.usmSectionName);
    utilFlush();
    printf("Professor Name (40 chars. max):");
    scanf("%[^\n]", usmSectionDetails.usmProfessor);
    if(usmDataExists(conn,usmSectionDetails.usmSectionName, "section_name", "section")){
        printf("\nSorry, %s already exists...\n", usmSectionDetails.usmSectionName);
        getch();
        imUsmSectionManagement(conn);
    }else{
        usmInsertSection(conn, usmSectionDetails);
        printf("Section Added! Succesful! ");
        getch();
        imUsmSectionManagement(conn);
    }
}

void usmInsertSection(MYSQL* conn, Section usmSec){
    string500 usmQuery;
    sprintf(usmQuery, "insert into section(section_name, professor_name) values ('%s','%s');",
            usmSec.usmSectionName,usmSec.usmProfessor);
    mysql_query(conn,usmQuery);
}

void usmUpdateSection(MYSQL* conn, Section usmSec, string30 usmSectionOrig){
    string500 usmQuery;
    sprintf(usmQuery, "update section set section_name = '%s', professor_name = '%s' where section_name = '%s';",
            usmSec.usmSectionName,usmSec.usmProfessor, usmSectionOrig);
    mysql_query(conn,usmQuery);
}

void usmEditSection(MYSQL* conn){
    Section usmSectionDetails;
    CLEARSCR;
    printf("Edit Section");
    printf("\nChoose Section:");
    string3 usmSectionName;
    usmChooseSections(conn, usmSectionName);
    CLEARSCR;
    utilFlush();
    printf("Editing: %s", usmSectionName);
    printf("\nSection Name (3 chars. max):");
    scanf("%s", usmSectionDetails.usmSectionName);
    utilFlush();
    printf("Professor Name (40 chars. max):");
    scanf("%[^\n]", usmSectionDetails.usmProfessor);
    if(strcmp(usmSectionName,usmSectionDetails.usmSectionName) && usmDataExists(conn,usmSectionDetails.usmSectionName, "section_name", "section")){
        printf("\nSorry, %s already exists", usmSectionDetails.usmSectionName);
        getch();
        imUsmSectionManagement(conn);
    }else{
        usmUpdateSection(conn, usmSectionDetails, usmSectionName);
        printf("\nSection Updated! Succesful! ");
        getch();
        imUsmSectionManagement(conn);
    }
}

void usmViewSection(MYSQL* conn){
    MYSQL_RES *res;
    MYSQL_ROW row;
    string500 usmQuery;
    sprintf(usmQuery, "select * from section;");
    mysql_query(conn,usmQuery);
    res = mysql_use_result(conn);
    //int usmInput;
    CLEARSCR;
    printf("\nSection Name\tProfessor");
    while((row = mysql_fetch_row(res)) != NULL){
        printf("\nS%s\t\t\t\t%s", row[1], row[2]);
    }
    mysql_free_result(res);
    
    printf("\n\nPress any key to continue...");
    utilFlush();
    getch();
    imUsmSectionManagement(conn);
}

/**PROJECT MANAGEMENT MODULE**/
Project pmConvertRow(MYSQL_ROW row, unsigned int numOfFields) {
    Project project;
    
    int fieldCtr;
    for (fieldCtr = 0; fieldCtr < numOfFields; fieldCtr++) {
        char* rowData = row[fieldCtr];
        if (fieldCtr == 0) {
            //user project id
            project.userProjectId = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 1) {
            //user id
            project.userId = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 2) {
            //psp id
            project.pspId = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 3) {
            //current phase
            project.currentPhase = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 4) {
            //done
            project.done = rowData == NULL ? 0 : atoi(rowData);
        }
        else {
            utilPrintError(NULL, "Unexpected column.");
        }
    }
    
    return project;
}

void pmGenerateProjects(MYSQL *conn, string40 userName){
    string500 usmQuery;
    int i;
    for(i = 1; i <= 5; i++){
        sprintf(usmQuery, "insert into project(user_id, psp_id,current_phase,done) values ((select user_id from user where user_name = '%s'),%d, (select phase_id from phase where phase_name = 'None'),0);",
                userName,i);
        if (mysql_query(conn,usmQuery) != 0) {
            utilPrintError(conn, "Could not generate projects for user");
        }
    }
}

void pmSetProjectDone(MYSQL* conn, string40 userName, int pspNum){
    string500 usmQuery;
    sprintf(usmQuery, "update project set done = 1 where user_id = (select user_id from user where user_name = '%s') and psp_id = %d;",
            userName, pspNum);
    mysql_query(conn,usmQuery);
}

void pmSetProjectDoneWithId(MYSQL* conn, int userId, int pspNum){
    string500 usmQuery;
    sprintf(usmQuery, "update project set done = 1 where user_id = %d and psp_id = %d;",
            userId, pspNum);
    mysql_query(conn,usmQuery);
}

/**SIZE ESTIMATING TEMPLATE**/
ObjectType stObjectTypeConvertRow(MYSQL_ROW row, unsigned long numOfFields) {
    int ctr;
    ObjectType objType;
    
    for (ctr = 0; ctr < numOfFields; ctr++) {
        char* rowData = row[ctr];
        if (ctr == 0) {
            objType.typeId = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (ctr == 1) {
            objType.typeName = malloc(sizeof(char) * (strlen(rowData) + 1));
            strcpy(objType.typeName, rowData);
        }
        else {
            utilPrintError(NULL, "Unexpected column.");
        }
    }
    
    return objType;
}

unsigned long stObjectTypeExecuteQuery(MYSQL* conn, char* query, ObjectType** objectTypes) {
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "OBJECT TYPE: Query failed.");
    }
    else {
        MYSQL_RES* result = mysql_store_result(conn);
        MYSQL_ROW row;
        unsigned long numOfRows = mysql_num_rows(result);
        unsigned long numOfFields = mysql_num_fields(result);
        ObjectType objType[numOfRows];
        int ctr = 0;
        
        if (numOfRows > 0) {
            while ((row = mysql_fetch_row(result)) != NULL) {
                objType[ctr] = stObjectTypeConvertRow(row, numOfFields);
                ctr++;
            }
            mysql_free_result(result);
            *objectTypes = objType;
            return numOfRows;
        }
        
        mysql_free_result(result);
    }
    
    *objectTypes = NULL;
    return 0;
}

unsigned long stGetObjectTypes(MYSQL* conn, ObjectType** objectTypes) {
    char query[1000];
    snprintf(query, 1000, "SELECT * FROM objecttype");
    
    ObjectType* objTypes = NULL;
    unsigned long numOfTypes = stObjectTypeExecuteQuery(conn, query, &objTypes);
    
    *objectTypes = objTypes;
    return numOfTypes;
}

ObjectType* stGetObjectTypeByTypeId(MYSQL* conn, int typeId) {
    char query[1000];
    snprintf(query, 1000, "SELECT * FROM objecttype WHERE type_id = %d", typeId);
    
    ObjectType* types = NULL;
    unsigned long numOfResults = stObjectTypeExecuteQuery(conn, query, &types);
    
    if (types == NULL || numOfResults == 0) {
        return NULL;
    }
    else {
        return types;
    }
}

SizeType stSizeTypeConvertRow(MYSQL_ROW row, unsigned long numOfFields) {
    int fieldCtr;
    SizeType sizeType;
    
    for (fieldCtr = 0; fieldCtr < numOfFields; fieldCtr++) {
        char* rowData = row[fieldCtr];
        if (fieldCtr == 0) {
            sizeType.sizeId = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 1) {
            sizeType.sizeName = malloc(sizeof(char) * (strlen(rowData) + 1));
            strcpy(sizeType.sizeName, rowData);
        }
        else {
            utilPrintError(NULL, "Unexpected column.");
        }
    }
    
    return sizeType;
}

unsigned long stSizeTypeExecuteQuery(MYSQL* conn, char* query, SizeType** sizeTypes) {
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "OBJECT TYPE: Query failed.");
    }
    else {
        MYSQL_RES* result = mysql_store_result(conn);
        MYSQL_ROW row;
        unsigned long numOfRows = mysql_num_rows(result);
        unsigned long numOfFields = mysql_num_fields(result);
        SizeType sizeType[numOfRows];
        int ctr = 0;
        
        if (numOfRows > 0) {
            while ((row = mysql_fetch_row(result)) != NULL) {
                sizeType[ctr] = stSizeTypeConvertRow(row, numOfFields);
                ctr++;
            }
            mysql_free_result(result);
            *sizeTypes = sizeType;
            return numOfRows;
        }
        
        mysql_free_result(result);
    }
    
    *sizeTypes = NULL;
    return 0;
}

unsigned long stGetSizeTypes(MYSQL* conn, SizeType** sizeTypes) {
    char query[1000];
    snprintf(query, 1000, "SELECT * FROM sizetype;");
    return stSizeTypeExecuteQuery(conn, query, sizeTypes);
}

SizeType* stGetSizeTypeByTypeId(MYSQL* conn, int typeId) {
    char query[1000];
    snprintf(query, 1000, "SELECT * FROM sizetype WHERE size_id = %d;", typeId);
    
    SizeType* types = NULL;
    unsigned long numOfResults = stSizeTypeExecuteQuery(conn, query, &types);
    
    if (numOfResults == 0 || types == NULL) {
        return NULL;
    }
    else {
        return types;
    }
}

ObjectSize stObjectSizeConvertRow(MYSQL_ROW row, unsigned long numOfFields) {
    int fieldCtr;
    ObjectSize size;
    
    for (fieldCtr = 0; fieldCtr < numOfFields; fieldCtr++) {
        char* rowData = row[fieldCtr];
        
        if (fieldCtr == 0) {
            size.objectSizeId = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 1) {
            size.typeId = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 2) {
            size.sizeId = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 3) {
            size.size = rowData == NULL ? 0 : atof(rowData);
        }
        else {
            utilPrintError(NULL, "Unexpected column.");
        }
    }
    
    return size;
}

unsigned long stObjectSizeExecuteQuery(MYSQL* conn, char* query, ObjectSize** objectSizes) {
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Cannot execute query.");
    }
    else {
        MYSQL_RES* result = mysql_store_result(conn);
        MYSQL_ROW row;
        unsigned long numOfRows = mysql_num_rows(result);
        unsigned long numOfFields = mysql_num_fields(result);
        ObjectSize objSizeType[numOfRows];
        int ctr = 0;
        
        if (numOfRows > 0) {
            while ((row = mysql_fetch_row(result)) != NULL) {
                objSizeType[ctr] = stObjectSizeConvertRow(row, numOfFields);
                ctr++;
            }
            mysql_free_result(result);
            *objectSizes = objSizeType;
            return numOfRows;
        }
        
        mysql_free_result(result);
    }
    
    *objectSizes = NULL;
    return 0;
}

ObjectSize* stGetObjectSizeById(MYSQL* conn, int objectSizeId) {
    char query[1000];
    snprintf(query, 1000, "SELECT * FROM objectsize WHERE objectsize_id = %d", objectSizeId);
    
    ObjectSize* sizes = NULL;
    unsigned long numOfRes = stObjectSizeExecuteQuery(conn, query, &sizes);
    
    if (numOfRes == 0 || sizes == NULL) {
        return NULL;
    }
    else {
        return sizes;
    }
}

SizeEstimate stSizeEstimateConvertRow(MYSQL_ROW row, unsigned long numOfFields) {
    int fieldCtr;
    SizeEstimate sizeEst;
    for (fieldCtr = 0; fieldCtr < numOfFields; fieldCtr++) {
        char* rowData = row[fieldCtr];
        
        if (fieldCtr == 0) {
            //template id
            sizeEst.template_id = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 1) {
            //user project id
            sizeEst.user_project_id = rowData == NULL ? -1 : atoi(rowData);
        }
        else {
            utilPrintError(NULL, "Unexpected column.");
        }
    }
    
    return sizeEst;
}

unsigned long stSizeEstimateExecuteQuery(MYSQL* conn, char* query, SizeEstimate** sizeEstimates) {
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Query failed.");
    }
    else {
        MYSQL_RES* result = mysql_store_result(conn);
        unsigned long numOfRows = mysql_num_rows(result);
        unsigned long numOfFields = mysql_num_fields(result);
        SizeEstimate sizeEsts[numOfRows];
        
        if (numOfRows > 0) {
            MYSQL_ROW row;
            int ctr = 0;
            
            while ((row = mysql_fetch_row(result)) != NULL) {
                sizeEsts[ctr] = stSizeEstimateConvertRow(row, numOfFields);
                ctr++;
            }
            mysql_free_result(result);
            *sizeEstimates = sizeEsts;            
            return numOfRows;
        }
        
        mysql_free_result(result);
    }
    
    *sizeEstimates = NULL;
    return 0;
}

SizeEstimate* stGetSizeEstimateById(MYSQL* conn, Project project) {
    SizeEstimate* sizeEstimate = NULL;
    char query[1000];
    snprintf(query, 1000, "SELECT * FROM sizeestimate WHERE user_project_id = %d", project.userProjectId);
    unsigned long numOfResults = stSizeEstimateExecuteQuery(conn, query, &sizeEstimate);
    
    if (numOfResults == 0) {
        return NULL;
    }
    else {
        return sizeEstimate;
    }
}

NewObject stNewObjectConvertRow(MYSQL_ROW row, unsigned long numOfFields) {
    int fieldCtr;
    NewObject newObj;
    
    for (fieldCtr = 0; fieldCtr < numOfFields; fieldCtr++) {
        char* rowData = row[fieldCtr];
        
        if (fieldCtr == 0) {
            //newobject_id
            newObj.newobject_id = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 1) {
            //template id
            newObj.template_id = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 2) {
            //function name
            strcpy(newObj.function_name, rowData);
        }
        else if (fieldCtr == 3) {
            //objectsize_id
            newObj.objectsize_id = rowData == NULL ? -1 : atoi(rowData);
        }
        else {
            utilPrintError(NULL, "Unexpected column.");
        }
    }
    
    return newObj;
}

unsigned long stNewObjectExecuteQuery(MYSQL* conn, char* query, NewObject** newObjects) {
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Cannot execute query.");
    }
    else {
        MYSQL_RES* result = mysql_store_result(conn);
        MYSQL_ROW row;
        unsigned long numOfRows = mysql_num_rows(result);
        unsigned long numOfFields = mysql_num_fields(result);
        NewObject newObjs[numOfRows];
        int ctr = 0;
        
        if (numOfRows > 0) {
            while ((row = mysql_fetch_row(result)) != NULL) {
                newObjs[ctr] = stNewObjectConvertRow(row, numOfFields);
                ctr++;
            }
            mysql_free_result(result);
            *newObjects = newObjs;
            return numOfRows;
        }
        
        mysql_free_result(result);
    }
    
    *newObjects = NULL;
    return 0;
}

unsigned long stGetNewObjectsForSizeEstimate(MYSQL* conn, SizeEstimate sizeEst, NewObject** newObjs) {
    char query[1000];
    
    snprintf(query, 1000, "SELECT * FROM newobject WHERE template_id = %d", sizeEst.template_id);
    
    NewObject* newObjects = NULL;
    unsigned long numOfResults = stNewObjectExecuteQuery(conn, query, &newObjects);
    
    *newObjs = newObjects;
    return numOfResults;
}

ReusedObject stReusedObjectConvertRow(MYSQL_ROW row, unsigned long numOfFields) {
    ReusedObject reusedObj;
    int fieldCtr;
    
    for (fieldCtr = 0; fieldCtr < numOfFields; fieldCtr++) {
        char* rowData = row[fieldCtr];
        
        if (fieldCtr == 0) {
            //reusedobject_id
            reusedObj.reusedobject_id = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 1) {
            //template_id
            reusedObj.template_id = rowData == NULL ? -1 : atoi(rowData);
        }
        else if (fieldCtr == 2) {
            //function name
            strcpy(reusedObj.function_name, rowData);
        }
        else if (fieldCtr == 3) {
            //function lines
            reusedObj.function_lines = rowData == NULL ? 0 : atoi(rowData);
        }
        else {
            utilPrintError(NULL, "Unexpected column.");
        }
    }
    
    return reusedObj;
}

unsigned long stReusedObjectExecuteQuery(MYSQL* conn, char* query, ReusedObject** reusedObjects) {
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Can't execute query.");
    }
    else {
        MYSQL_RES* result = mysql_store_result(conn);
        MYSQL_ROW row;
        unsigned long numOfRows = mysql_num_rows(result);
        unsigned long numOfFields = mysql_num_fields(result);
        ReusedObject reusedObjs[numOfRows];
        int ctr = 0;
        
        if (numOfRows > 0) {
            while ((row = mysql_fetch_row(result)) != NULL) {
                reusedObjs[ctr] = stReusedObjectConvertRow(row, numOfFields);
                ctr++;
            }
            mysql_free_result(result);
            *reusedObjects = reusedObjs;
            return numOfRows;
        }
        
        mysql_free_result(result);
    }
    
    *reusedObjects = NULL;
    return 0;
}

unsigned long stGetReusedObjectsForSizeEstimate(MYSQL* conn, SizeEstimate sizeEst, ReusedObject** reusedObjs) {
    char query[1000];
    snprintf(query, 1000, "SELECT * FROM reusedobject WHERE template_id = %d", sizeEst.template_id);
    
    ReusedObject* reusedObjects = NULL;
    unsigned long numOfResults = stReusedObjectExecuteQuery(conn, query, &reusedObjects);
    
    *reusedObjs = reusedObjects;
    return numOfResults;
}

int stGetObjectSizeId(MYSQL* conn, int type, int size)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    //MYSQL_FIELD *field;
    int num_fields, i, objectSizeID;
    
    char check[1000];
    sprintf(check, "SELECT objectsize_id FROM objectsize WHERE type_id = %d AND size_id = %d LIMIT 1;", type, size);
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            objectSizeID = atoi(value);
        }
    }
    mysql_free_result(result);
    
    return objectSizeID;
}

int stSetNewObject(MYSQL* conn, NewObject newObject, int type, int size)
{
    int success = 1;
    
    newObject.objectsize_id = stGetObjectSizeId(conn, type,size);
    
    char query[1000];
    sprintf(query, "INSERT INTO newobject (template_id,function_name,objectsize_id) VALUES(%d,\"%s\",%d);",
            newObject.template_id,
            newObject.function_name,
            newObject.objectsize_id);
    if(mysql_query(conn, query) != 0)
    {
        success = 0;
    }
    
    return success;
}

int stSetReusedObject(MYSQL* conn, ReusedObject reusedObject)
{
    int success = 1;
    
    char query[1000];
    sprintf(query, "INSERT INTO reusedobject (template_id,function_name,function_lines) VALUES(%d,\"%s\",%f);",
            reusedObject.template_id,
            reusedObject.function_name,
            reusedObject.function_lines);
    if(mysql_query(conn, query) != 0)
    {
        success = 0;
    }
    
    return success;
}

float stGetTotalNewObjects(MYSQL* conn, SizeEstimate sizeEstimate)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    //    MYSQL_FIELD *field;
    int num_fields, i;
    float total;
    char check[1000];
    sprintf(check, "SELECT SUM(objectsize.size) AS SUM FROM newobject, objecttype, objectsize WHERE newobject.objectsize_id = objectsize.objectsize_id AND objecttype.type_id = objectsize.type_id AND newobject.template_id = %d;", sizeEstimate.template_id);
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            total = atof(value);
        }
    }
    mysql_free_result(result);
    
    return total;
}

int stGetReusedTotal(MYSQL* conn, SizeEstimate sizeEstimate)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    //MYSQL_FIELD *field;
    int num_fields, i;
    int total;
    
    char check[1000];
    sprintf(check, "SELECT SUM(reusedobject.function_lines) AS SUM FROM reusedobject WHERE template_id = %d;", sizeEstimate.template_id);
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            total = atoi(value);
        }
    }
    mysql_free_result(result);
    
    return total;
}

int stHasSizeEstimate(MYSQL* conn, Project project) {
    MYSQL_RES *result;
    
    char query[1000];
    sprintf(query, "SELECT * FROM sizeestimate WHERE user_project_id = %d;", project.userProjectId);
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Query could not be executed...");
        return 0;
    }
    else {
        result = mysql_store_result(conn);
        
        unsigned long numOfRows = mysql_num_rows(result);
        
        mysql_free_result(result);
        
        if (numOfRows == 0) {
            return 0;
        }
        else {
            return 1;
        }
    }
}

unsigned long stSetSizeEstimate(MYSQL* conn, Project project) {
    char insert[1000];
    sprintf(insert, "INSERT INTO sizeestimate (user_project_id) VALUES (%d);", project.userProjectId);
    
    if (mysql_query(conn, insert) != 0) {
        utilPrintError(conn, "The insertion was unsuccessful");
        return -1;
    }
    else {
        return mysql_insert_id(conn);
    }
}

/**SCHEDULE PLANNING TEMPLATE**/
int sptGetTaskPlanIDFromProject(MYSQL* conn, Project project)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
//    MYSQL_FIELD *field;
    int num_fields,i;
    
    int id;
    
    char check[1000];
    sprintf(check, "SELECT * FROM taskplan WHERE user_project_id = %d", project.userProjectId);
    mysql_query(conn, check);
    result = mysql_store_result(conn);

    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            switch(i)
            {
                case 0: id = atoi(value);
                    break;
                case 1:
                    break;
            }
        }
    }
    
    mysql_free_result(result);
    
    return id;
}

float sptGetHoursFromPhase(MYSQL* conn, Project project, int phase_id)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    //MYSQL_FIELD *field;
    int num_fields,i;
    
    float hours;
    
    char check[1000];
    sprintf(check, "SELECT * FROM plannedtime WHERE plan_id = %d", ppsGetPlanIDFromProject(conn, project));
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            switch(i)
            {
                case 2: if(phase_id == 1 ) hours = atof(value) / 60;
                    break;
                case 3: if(phase_id == 2 ) hours = atof(value) / 60;
                    break;
                case 4: if(phase_id == 3 ) hours = atof(value) / 60;
                    break;
                case 5: if(phase_id == 4 ) hours = atof(value) / 60;
                    break;
                case 6: if(phase_id == 5 ) hours = atof(value) / 60;
                    break;
                case 7: if(phase_id == 6 ) hours = atof(value) / 60;
                    break;
            }
        }
    }

    mysql_free_result(result);
    
    return hours;
}

float sptGetTotalHoursPPS(MYSQL* conn, Project project, int phase_id)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
//    MYSQL_FIELD *field;
    int num_fields, i;
    
    float hours;
    
    char check[1000];
    sprintf(check, "SELECT * FROM plannedtime WHERE plan_id = %d", ppsGetPlanIDFromProject(conn, project));
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            switch(i)
            {
                case 2: if(phase_id == 1 ) hours = atof(value) / 60;
                    break;
                case 3: if(phase_id == 2 ) hours = atof(value) / 60;
                    break;
                case 4: if(phase_id == 3 ) hours = atof(value) / 60;
                    break;
                case 5: if(phase_id == 4 ) hours = atof(value) / 60;
                    break;
                case 6: if(phase_id == 5 ) hours = atof(value) / 60;
                    break;
                case 7: if(phase_id == 6 ) hours = atof(value) / 60;
                    break;
            }
        }
    }
    
    mysql_free_result(result);
    
    return hours;
}

float sptGetActualHoursFromPhase(MYSQL* conn, Project project, int phase_id)
{
    float hours;
    
    Phase* phase = priGetPhaseById(conn, phase_id);
    
    Record* records = trlGetTimeLogForPhase(conn, project, *phase);
    Interruption* interruptions = NULL;
    unsigned long numOfInterruptions = trlGetInterruptionsForPhase(conn, project, *phase, &interruptions);
    double deltaSeconds = trlGetDeltaTime(*records, interruptions, numOfInterruptions, NULL);
    
    hours = (deltaSeconds / 60) / 60;
    return hours;
}

sptRow * sptGetRows(MYSQL* conn, Project project)
{
    time_t date_planned;
    struct tm *date;
    char buffer[10];
    int counter = 0;
    long * dates;
    float hours;
    
    sptRow * sptRowData;
    
    MYSQL_RES *result;
    MYSQL_ROW row;
//    MYSQL_FIELD *field;
    
    int num_fields, i;
    unsigned long num_rows;
    
    char check[1000];
    sprintf(check, "SELECT DISTINCT date_planned FROM taskplanentry WHERE taskplan_id = %d", sptGetTaskPlanIDFromProject(conn, project));
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    num_rows = mysql_num_rows(result);
    
    sptRowData = malloc(num_rows*sizeof(sptRowData));
    dates = (long*) malloc(num_rows*sizeof(long));
    
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            dates[counter] = atol(value);
        }
        counter++;
    }
    
    mysql_free_result(result);
    
    //get the number, and date
    for(i=0; i<counter; i++)
    {
        hours=0;
        date_planned = dates[i];
        date = localtime(&date_planned);
        sprintf(buffer,"%d/%d/%d",date->tm_mon+1,date->tm_mday,date->tm_year+1900);
        
        sptRowData[i].number = i+1;
        //printf("D%d\n", sptRowData[i].number);
        strcpy(sptRowData[i].date,buffer);
        //printf("DATE: %s\n",sptRowData[i].date);
        printf("D%d PLANNED DIRECT HOURS: ",sptRowData[i].number);
        scanf("%f", &sptRowData[i].planDirectHours);
        
        if(i > 0)
        {
            sptRowData[i].planCumulativeHours = sptRowData[i-1].planDirectHours + sptRowData[i].planDirectHours;
            //printf("PLANNED CUMULATIVE HOURS: %f",sptRowData[i].planCumulativeHours);
        }
        else
        {
            sptRowData[i].planCumulativeHours = sptRowData[i].planDirectHours;
            //printf("PLANNED CUMULATIVE HOURS: %f",sptRowData[i].planCumulativeHours);
        }
        
        // calculate cumulative hours from plannedtime
        char check[1000];
        int ii;
        sprintf(check, "SELECT phase_id FROM taskplanentry WHERE date_planned = %ld", dates[i]);
        mysql_query(conn, check);
        result = mysql_store_result(conn);
        
        num_fields = mysql_num_fields(result);
        num_rows = mysql_num_rows(result);
        
        while ((row = mysql_fetch_row(result)))
        {
            for(ii=0; ii<num_fields; ii++)
            {
                char value[100];
                sprintf(value,"%s", row[ii] ? row[ii] : "NULL");
                hours += sptGetHoursFromPhase(conn, project, atoi(value));
                
                if(sptRowData[i].planCumulativeHours >= hours)
                {
                    sptRowData[i].planCumulativePlannedValue = hours;
                }
            }
        }
        
        mysql_free_result(result);
        
        //printf("\nCUMULATIVE PLANNED VALUE: %f\n", sptRowData[i].planCumulativePlannedValue);
        
        // calculate actual direct hours from time log
        hours = 0;
        char check2[1000];
        int iii;
        sprintf(check2, "SELECT phase_id FROM taskplanentry WHERE date_planned = %ld", dates[i]);
        mysql_query(conn, check2);
        result = mysql_store_result(conn);
        
        num_fields = mysql_num_fields(result);
        num_rows = mysql_num_rows(result);
        
        while ((row = mysql_fetch_row(result)))
        {
            for(iii=0; iii<num_fields; iii++)
            {
                char value[100];
                sprintf(value,"%s", row[iii] ? row[iii] : "NULL");
                hours += sptGetActualHoursFromPhase(conn, project, atoi(value));
            }
        }
        
        mysql_free_result(result);
        
        sptRowData[i].actualDirectHours = hours;
        //printf("ACTUAL DIRECT HOURS: %f\n", sptRowData[i].actualDirectHours);
        
        if(i > 0)
        {
            sptRowData[i].actualCumulativeHours = sptRowData[i-1].actualDirectHours + sptRowData[i].actualDirectHours;
            //printf("ACTUAL CUMULATIVE HOURS: %f\n",sptRowData[i].actualCumulativeHours);
        }
        else
        {
            sptRowData[i].actualCumulativeHours = sptRowData[i].actualDirectHours;
            //printf("ACTUAL CUMULATIVE HOURS: %f\n",sptRowData[i].actualCumulativeHours);
        }
        
        // calculate cumulative earned value
        hours = 0;
        char check3[1000];
        int iiii;
        sprintf(check3, "SELECT phase_id FROM taskplanentry WHERE date_planned = %ld", dates[i]);
        mysql_query(conn, check3);
        result = mysql_store_result(conn);
        
        num_fields = mysql_num_fields(result);
        num_rows = mysql_num_rows(result);
        
        while ((row = mysql_fetch_row(result)))
        {
            for(iiii=0; iiii<num_fields; iiii++)
            {
                char value[100];
                sprintf(value,"%s", row[iiii] ? row[iiii] : "NULL");
                hours += sptGetActualHoursFromPhase(conn, project, atoi(value));
            }
        }
        
        mysql_free_result(result);
        
        ppsGetTotalActualTime(conn, project);
        
        //printf("ACTUAL CUMULATIVE EARNED VALUE: \n");
        //printf("ADJUSTED EARNED VALUE: \n");
        
        sptGetActualHoursFromPhase(conn, project,1);
        
        //printf("\n");
        mysql_free_result(result);
        
    }
    
    free(dates);
    
    return sptRowData;
}


/**PROJECT PLAN SUMMARY**/
unsigned long ppsInsertPlan(MYSQL* conn, PlanSummary planSummary) {
    char query[1000];
    sprintf(query, "INSERT INTO %s (%s) VALUES (%d);", PlanSummaryTable.tableName, PlanSummaryTable.userProjectId, planSummary.userProjectId);
    
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Cannot execute insert.");
        return -1;
    }
    else {
        return mysql_insert_id(conn);
    }
}

PlanSummary ppsSetProjectPlan(MYSQL* conn, Project project) {
    PlanSummary planSummary;
    planSummary.userProjectId = project.userProjectId;
    unsigned long id = ppsInsertPlan(conn, planSummary);
    planSummary.planId = (int)id;
    return planSummary;
}

int ppsHasPlanForProject (MYSQL* conn, Project project) {
    char query[1000];
    sprintf(query, "SELECT * FROM %s WHERE %s = %d;", PlanSummaryTable.tableName, PlanSummaryTable.userProjectId, project.userProjectId);
    
    if (mysql_query(conn, query) != 0) {
        utilPrintError(conn, "Cannot execute query.");
        return 0;
    }
    else {
        MYSQL_RES* res = mysql_use_result(conn);
        unsigned long numOfPlans = mysql_num_rows(res);
        mysql_free_result(res);
        if (numOfPlans == 0) {
            return 0;
        }
        else {
            return 1;
        }
    }
}

void ppsSetPlannedTimeDB(MYSQL* conn, Project project, PlannedTime plannedTime)
{
    if(project.pspId != 1)
    {
        char query[1000];
        sprintf(query, "INSERT INTO plannedtime (plan_id,planningTime,designTime,codeTime,compileTime,testTime,postMortemTime,totalTime) VALUES(%d,%d,%d,%d,%d,%d,%d,%d);",
                plannedTime.planId,
                plannedTime.planningTime,
                plannedTime.designTime,
                plannedTime.codeTime,
                plannedTime.compileTime,
                plannedTime.testTime,
                plannedTime.postMortemTime,
                plannedTime.totalTime);
        if (mysql_query(conn, query) != 0) {
            utilPrintError(conn, "Insert failed.");
        }
    }
    else
    {
        char query[1000];
        sprintf(query, "INSERT INTO plannedtime (plan_id,totalTime) VALUES(%d,%d);",
                plannedTime.planId,
                plannedTime.totalTime);
        if (mysql_query(conn, query) != 0) {
            utilPrintError(conn, "Insert failed.");
        }
    }
}

int ppsPlannedTimeCheck(MYSQL* conn, PlannedTime plannedTime)
{
    MYSQL_RES *result;
    
    int returnVal = 0;
    
    char check[1000];
    sprintf(check, "SELECT * FROM plannedtime WHERE plan_id = %d;", plannedTime.planId);
    if (mysql_query(conn, check) != 0) {
        utilPrintError(conn, "PLANNED TIME CHECK: Query failed.");
    }
    else {
        result = mysql_store_result(conn);
        
        if(mysql_num_rows(result)>=1)
        {
            returnVal = 1;
        }
        else
        {
            returnVal = 0;
        }
        
        mysql_free_result(result);
    }
    
    return returnVal;
}

void ppsSetPlannedTime(MYSQL* conn, Project project)
{
    PlannedTime plannedTime;
    int totalTime;
    
    plannedTime.planId = ppsGetPlanIDFromProject(conn, project);
    
    if(ppsPlannedTimeCheck(conn, plannedTime) != 1)
    {
        printf("\nTime in Phase (in minutes)\n\n");
        
        if(project.pspId != 1)
        {
            printf("Planning: ");
            scanf("%d", &plannedTime.planningTime);
            
            printf("Design: ");
            scanf("%d", &plannedTime.designTime);
            
            printf("Code: ");
            scanf("%d", &plannedTime.codeTime);
            
            printf("Compile: ");
            scanf("%d", &plannedTime.compileTime);
            
            printf("Test: ");
            scanf("%d", &plannedTime.testTime);
            
            printf("Postmortem: ");
            scanf("%d", &plannedTime.postMortemTime);
            
            ppsSetPlannedTimeDB(conn, project,plannedTime);
            
            totalTime =
            plannedTime.planningTime +
            plannedTime.designTime +
            plannedTime.codeTime +
            plannedTime.compileTime +
            plannedTime.testTime +
            plannedTime.postMortemTime;
        }
        else
        {
            printf("Total: ");
            scanf("%d", &plannedTime.totalTime);
            
            ppsSetPlannedTimeDB(conn, project,plannedTime);
        }
    }
    else
    {
        printf("You've already filled this up.\n");
    }
}

PlannedTime ppsGetPlannedTime(MYSQL* conn, Project project)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    //MYSQL_FIELD *field;
    int num_fields,i;
    PlannedTime plannedTime;
    
    char check[1000];
    sprintf(check, "SELECT * FROM plannedtime WHERE plan_id = %d;", ppsGetPlanIDFromProject(conn, project));
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            switch(i)
            {
                case 0: plannedTime.plannedTimeId = atoi(value);
                    break;
                case 1: plannedTime.planId = atoi(value);
                    break;
                case 2: plannedTime.planningTime = atoi(value);
                    break;
                case 3: plannedTime.designTime = atoi(value);
                    break;
                case 4: plannedTime.codeTime = atoi(value);
                    break;
                case 5: plannedTime.compileTime = atoi(value);
                    break;
                case 6: plannedTime.testTime = atoi(value);
                    break;
                case 7: plannedTime.postMortemTime = atoi(value);
                    break;
            }
        }
    }
    
    mysql_free_result(result);
    
    return plannedTime;
}

void ppsSetPlannedLOCDB(MYSQL* conn, Project project, PlannedLOC plannedLOC)
{
    if(project.pspId >= 4)
    {
        char query[1000];
        sprintf(query, "INSERT INTO plannedloc (plan_id,planBaseLOC,planDeletedLOC,planModifiedLOC,planAddedLOC,planReusedLOC,planTotalNewAndChanged) VALUES (%d,%f,%f,%f,%f,%f,%f);",
                plannedLOC.planId,
                plannedLOC.planBaseLOC,
                plannedLOC.planDeletedLOC,
                plannedLOC.planModifiedLOC,
                plannedLOC.planAddedLOC,
                plannedLOC.planReusedLOC,
                plannedLOC.planTotalNewAndChanged);
        mysql_query(conn, query);
    }
    else
    {
        char query[1000];
        sprintf(query, "INSERT INTO plannedloc (plan_id,planTotalNewAndChanged) VALUES (%d,%f);",
                plannedLOC.planId,
                plannedLOC.planTotalNewAndChanged);
        mysql_query(conn, query);
    }
}

int ppsPlannedLOCCheck(MYSQL* conn, PlannedLOC plannedLOC)
{
    MYSQL_RES *result;
    
    int returnVal = 0;
    
    char check[1000];
    sprintf(check, "SELECT * FROM plannedloc WHERE plan_id = %d;", plannedLOC.planId);
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    if(mysql_num_rows(result)>=1)
    {
        returnVal = 1;
    }
    else
    {
        returnVal = 0;
    }
    
    mysql_free_result(result);
    
    return returnVal;
}

void ppsSetPlannedLOC(MYSQL* conn, Project project)
{
    PlannedLOC plannedLOC;
    int totalNewAndChanged, totalLOC;
    
    plannedLOC.planId = ppsGetPlanIDFromProject(conn, project);
    
    if(ppsPlannedLOCCheck(conn, plannedLOC) != 1)
    {
        printf("\nProgram Size (LOC)\n\n");
        
        if(project.pspId >= 4)
        {
            printf("Base (B): ");
            scanf("%f", &plannedLOC.planBaseLOC);
            
            printf("  Deleted (D): ");
            scanf("%f", &plannedLOC.planDeletedLOC);
            
            printf("  Modified (M): ");
            scanf("%f", &plannedLOC.planModifiedLOC);
            
            printf("  Added (A): ");
            scanf("%f", &plannedLOC.planAddedLOC);
            
            printf("  Reused (R): ");
            scanf("%f", &plannedLOC.planReusedLOC);
            
            printf("Total New and Changed (N): "); // should actually be retrieved from Size Estimating Template
            scanf("%f", &plannedLOC.planTotalNewAndChanged);
            
            ppsSetPlannedLOCDB(conn, project,plannedLOC);
            
            printf("Total LOC (T): "); // T = N+B-M-D+R
            scanf("%d", &totalLOC);
            
            printf("Total New Reused: "); // ????
            scanf("%d", &totalNewAndChanged);
        }
        else
        {
            printf("Total New and Changed (N): ");
            scanf("%f", &plannedLOC.planTotalNewAndChanged);
            
            ppsSetPlannedLOCDB(conn, project,plannedLOC);
        }
    }
    else
    {
        printf("You've already filled this up.\n");
    }
}

PlannedLOC ppsGetPlannedLOC(MYSQL* conn, Project project)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    //MYSQL_FIELD *field;
    int num_fields,i;
    PlannedLOC plannedLOC;
    
    char check[1000];
    sprintf(check, "SELECT * FROM plannedloc WHERE plan_id = %d;", ppsGetPlanIDFromProject(conn, project));
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            switch(i)
            {
                case 0: plannedLOC.plannedLOCId = atoi(value);
                    break;
                case 1: plannedLOC.planId = atoi(value);
                    break;
                case 2: plannedLOC.planBaseLOC = atof(value);
                    break;
                case 3: plannedLOC.planDeletedLOC= atof(value);
                    break;
                case 4: plannedLOC.planModifiedLOC = atof(value);
                    break;
                case 5: plannedLOC.planAddedLOC= atof(value);
                    break;
                case 6: plannedLOC.planReusedLOC = atof(value);
                    break;
                case 7: plannedLOC.planTotalNewAndChanged = atof(value);
                    break;
            }
        }
    }
    
    mysql_free_result(result);
    
    return plannedLOC;
}

void ppsSetActualLOCDB(MYSQL* conn, Project project, ActualLOC actualLOC)
{
    if(project.pspId >= 2)
    {
        char query[1000];
        sprintf(query, "INSERT INTO actualloc (plan_id,actualBaseLOC,actualDeletedLOC,actualModifiedLOC,actualAddedLOC,actualReusedLOC,actualTotalNewAndChanged) VALUES (%d,%d,%d,%d,%d,%d,%d);",
                actualLOC.planId,
                actualLOC.actualBaseLOC,
                actualLOC.actualDeletedLOC,
                actualLOC.actualModifiedLOC,
                actualLOC.actualAddedLOC,
                actualLOC.actualReusedLOC,
                actualLOC.actualTotalNewAndChanged);
        mysql_query(conn, query);
    }
    else
    {
        
        char query[1000];
        sprintf(query, "INSERT INTO plannedloc (plan_id) VALUES (%d);",
                actualLOC.planId);
        mysql_query(conn, query);
    }
}

int ppsSetActualLOCCheck(MYSQL* conn, ActualLOC actualLOC)
{
    MYSQL_RES *result;
    
    int returnVal = 0;
    
    char check[1000];
    sprintf(check, "SELECT * FROM actualloc WHERE plan_id = %d;", actualLOC.planId);
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    if(mysql_num_rows(result)>=1)
    {
        returnVal = 1;
    }
    else
    {
        returnVal = 0;
    }
    
    mysql_free_result(result);
    
    return returnVal;
}

void ppsSetActualLOC(MYSQL* conn, Project project)
{
    ActualLOC actualLOC;
    int totalNewAndChanged, totalLOC;
    
    actualLOC.planId = ppsGetPlanIDFromProject(conn, project);
    
    printf("\nProgram Size (LOC)\n\n");
    
    if(ppsSetActualLOCCheck(conn, actualLOC) != 1)
    {
        if(project.pspId >= 2)
        {
            printf("Base (B): ");
            scanf("%d", &actualLOC.actualBaseLOC);
            
            printf("  Deleted (D): ");
            scanf("%d", &actualLOC.actualDeletedLOC);
            
            printf("  Modified (M): ");
            scanf("%d", &actualLOC.actualModifiedLOC);
            
            printf("  Added (A): ");
            scanf("%d", &actualLOC.actualAddedLOC);
            
            printf("  Reused (R): ");
            scanf("%d", &actualLOC.actualReusedLOC);
            
            printf("Total New and Changed (N): "); // should actually be retrieved from Size Estimating Template
            scanf("%d", &actualLOC.actualTotalNewAndChanged);
            
            ppsSetActualLOCDB(conn, project,actualLOC);
            
            printf("Total LOC (T): "); // T = N+B-M-D+R
            scanf("%d", &totalLOC);
            
            printf("Total New Reused: "); // ????
            scanf("%d", &totalNewAndChanged);
        }
        else
        {
            /*
             printf("Total New and Changed (N): ");
             scanf("%f", &actualLOC.planTotalNewAndChanged);
             
             ppsSetPlannedLOCDB(conn, project,plannedLOC);
             */
        }
    }
    else
    {
        printf("You've already filled this up.\n");
    }
}

ActualLOC ppsGetActualLOC(MYSQL* conn, Project project)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    //MYSQL_FIELD *field;
    int num_fields,i;
    
    ActualLOC actualLOC;
    
    actualLOC.actualBaseLOC = 0;
    actualLOC.actualDeletedLOC = 0;
    actualLOC.actualModifiedLOC = 0;
    actualLOC.actualAddedLOC = 0;
    actualLOC.actualReusedLOC = 0;
    actualLOC.actualTotalNewAndChanged = 0;
    
    char check[1000];
    sprintf(check, "SELECT * FROM actualloc WHERE plan_id = %d;", ppsGetPlanIDFromProject(conn, project));
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            switch(i)
            {
                case 0: actualLOC.actualLOCId = atoi(value);
                    break;
                case 1: actualLOC.planId = atoi(value);
                    break;
                case 2: actualLOC.actualBaseLOC = atoi(value);
                    break;
                case 3: actualLOC.actualDeletedLOC= atoi(value);
                    break;
                case 4: actualLOC.actualModifiedLOC = atoi(value);
                    break;
                case 5: actualLOC.actualAddedLOC= atoi(value);
                    break;
                case 6: actualLOC.actualReusedLOC = atoi(value);
                    break;
                case 7: actualLOC.actualTotalNewAndChanged = atoi(value);
                    break;
            }
        }
    }
    
    mysql_free_result(result);
    
    return actualLOC;
}

PlannedTime ppsGetActualTime(MYSQL* conn, Project project)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    //MYSQL_FIELD *field;
    int num_fields,i;
    
    PlannedTime actualTime;
    long start, end, delta;
    int counter;
    
    for(counter=1; counter<=6; counter++)
    {
        //Traverse phases
        char check[1000];
        sprintf(check, "SELECT * FROM timelog WHERE user_project_id = %d AND phase_id = %d;", project.userProjectId, counter);
        
        if(mysql_query(conn, check)==0)
        {
            result = mysql_store_result(conn);
            num_fields = mysql_num_fields(result);
            while ((row = mysql_fetch_row(result)))
            {
                for(i = 0; i < num_fields; i++)
                {
                    char value[100];
                    sprintf(value,"%s", row[i] ? row[i] : "NULL");
                    
                    switch(i)
                    {
                        case 3: start = atoi(value);
                            break;
                        case 4: end = atoi(value);
                            break;
                    }
                }
            }
            
            mysql_free_result(result);
            
            delta = end - start; // convert this to minutes
            
            switch(counter)
            {
                case 1: actualTime.planningTime = (int)delta;
                    break;
                case 2: actualTime.designTime = (int)delta;
                    break;
                case 3: actualTime.codeTime = (int)delta;
                    break;
                case 4: actualTime.compileTime = (int)delta;
                    break;
                case 5: actualTime.testTime = (int)delta;
                    break;
                case 6: actualTime.postMortemTime = (int)delta;
                    break;
            }
        }
        //end
    }
    
    
    mysql_free_result(result);
    
    return actualTime;
}

int ppsGetTotalActualTime(MYSQL* conn, Project project)
{
    float total;
    PlannedTime actualTime = ppsGetActualTime(conn, project);
    total = actualTime.planningTime +
    actualTime.designTime +
    actualTime.codeTime +
    actualTime.compileTime +
    actualTime.testTime +
    actualTime.postMortemTime;
    
    //printf("TOTAL ACTUAL TIME!!! %f\n", total);
    
    return 0;
}

DefectsInjected ppsGetDefectsInjected(Project project)
{
    DefectsInjected injected;
    /* retrieve from DB? */
    return injected;
}

DefectsRemoved ppsGetDefectsRemoved(Project project)
{
    DefectsRemoved removed;
    /* retrieve from DB? */
    return removed;
}

float ppsTotalPlannedLOC(PlannedLOC plannedLOC)
{
    float t;
    t = plannedLOC.planTotalNewAndChanged +
    plannedLOC.planBaseLOC -
    plannedLOC.planModifiedLOC -
    plannedLOC.planDeletedLOC +
    plannedLOC.planReusedLOC;
    
    return t;
}

int ppsTotalActualLOC(ActualLOC actualLOC)
{
    int t;
    t = actualLOC.actualTotalNewAndChanged +
    actualLOC.actualBaseLOC -
    actualLOC.actualModifiedLOC -
    actualLOC.actualDeletedLOC +
    actualLOC.actualReusedLOC;
    
    return t;
}

int ppsGetPlanIDFromProject(MYSQL* conn, Project project)
{
    MYSQL_RES *result;
    MYSQL_ROW row;
    //MYSQL_FIELD *field;
    int num_fields,i;
    
    int id;
    
    char check[1000];
    sprintf(check, "SELECT * FROM plansummary WHERE user_project_id = %d;", project.userProjectId);
    mysql_query(conn, check);
    result = mysql_store_result(conn);
    
    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result)))
    {
        for(i = 0; i < num_fields; i++)
        {
            char value[100];
            sprintf(value,"%s", row[i] ? row[i] : "NULL");
            
            switch(i)
            {
                case 0: id = atoi(value);
                    break;
                case 1:
                    break;
            }
        }
    }
    
    mysql_free_result(result);
    
    return id;
}

/**UI INTEGRATION**/

int imSelectFromChoices(char* choices[], unsigned long numOfChoices, char* optionalPrompt) {
	int choice;
	int choiceCtr;
	int showErrorMessage = 0;

	for (choiceCtr = 0; choiceCtr < numOfChoices; choiceCtr++) {
		printf("\t[%d] %s\n", choiceCtr + 1, choices[choiceCtr]);
	}
	printf("\n");

	do {
		if (showErrorMessage == 1) {
			printf("The input that was received was invalid. Please try again.\n");
		}

		if (optionalPrompt == NULL) {
			printf("Enter the correct number: ");
		}	
		else {
			printf("%s: ", optionalPrompt);
		}
		scanf("%d", &choice);
		showErrorMessage = 1;
	} while (choice <= 0 || choice > numOfChoices);

	return choice;
}

int imSelectYesOrNo(char* optionalPrompt) {
	char* choices[] = {"Yes", "No"};
	return imSelectFromChoices(choices, 2, optionalPrompt);
}

int imInputNumberRange (int lowerBound, int upperBound, char* optionalMessage, char* optionalErrorMessage) {
    int error = 0;
    int number = 0;
    
    do {
		if (error == 1) {
            if (optionalErrorMessage == NULL) {
                printf("You did not enter a valid number!\n");
            }
            else {
                printf("%s\n", optionalErrorMessage);
            }
		}
        
        if (optionalMessage == NULL) {
            printf("Please enter a number between %d and %d: ", lowerBound, upperBound);
        }
        else {
            printf("%s: ", optionalMessage);
        }
		scanf("%d", &number);
		error = 1;
	} while (number < lowerBound || number > upperBound);
    
    return number;
}

/**PHASE RETRIEVAL UI**/
Phase* imPriPhaseSelect (MYSQL* conn, int cancelEnabled) {
	
	printf("Select a phase.\n\n");
	
	Phase* phases = NULL;
	unsigned long numberOfPhases = priGetPhases(conn, &phases);
	int phaseCtr;
    
	for (phaseCtr = 0; phaseCtr < numberOfPhases; phaseCtr++) {
		Phase currentPhase = *(phases + phaseCtr);
		printf("%d - %s\n", phaseCtr + 1, currentPhase.phaseName);
	}
    
    if (cancelEnabled) {
        printf("%d - Cancel\n", phaseCtr + 1);
    }
    
    
	printf("\n");
	int phaseChoice = 0;
	int error = 0;
    
	do {
		if (error == 1) {
			printf("Invalid input. Please within the range of 1 to %ld.\n", numberOfPhases);
		}
		printf("Type phase number: ");
		scanf("%d", &phaseChoice);
		error = 1;
	} while (phaseChoice > numberOfPhases + 1 || phaseChoice <= 0);
    
	if (cancelEnabled && phaseChoice == numberOfPhases + 1) {
		//exit
		return NULL;
	}
	else {
		Phase* phaseToReturn = malloc(sizeof(Phase));
		*phaseToReturn = *(phases + (phaseChoice - 1));
		free(phases);
        
		return phaseToReturn;
	}
}

/**TASK PLANNING TEMPLATE UI**/
unsigned long imTptConvertTaskPlanDisplays (MYSQL* conn, Project project, TaskPlanEntry* entries, unsigned long numOfEntries, TaskPlanDisplay** printedEntries) {
	if (entries == NULL) {
		return 0;
	}
    
	float cumulativeHours = 0.0;
	float plannedValue = 0.0;
	float cumulativePlannedValue = 0.0;
	TaskPlanDisplay* displays = malloc(sizeof(TaskPlanDisplay)*numOfEntries);
    
    PlannedTime planTime = ppsGetPlannedTime(conn, project);
    
	int entryCtr;
	for (entryCtr = 0; entryCtr < numOfEntries; entryCtr++) {
		TaskPlanEntry currentEntry = *(entries + entryCtr);
		TaskPlanDisplay* currentDisplay = displays + entryCtr;
		currentDisplay->number = entryCtr + 1;
		
		Phase* phase = priGetPhaseById(conn, currentEntry.phaseId);
		if (phase == NULL) {
			char* phaseName = "Undefined\t";
			unsigned long bufferSize = strlen(phaseName) + 1;
			currentDisplay->name = malloc(sizeof(char)*bufferSize);
			strncpy(currentDisplay->name, phaseName, bufferSize);
		}
		else {
			unsigned long bufferSize = strlen(phase->phaseName) + 1;
			currentDisplay->name = malloc(sizeof(char)*bufferSize);
			snprintf(currentDisplay->name, bufferSize, "%s\t", phase->phaseName);
			free(phase);
		}
        
		//get from pps
        int hours = 0;
		
        switch (phase->phaseId) {
            case 1:
                hours = planTime.planningTime;
                break;
            case 2:
                hours = planTime.designTime;
                break;
            case 3:
                hours = planTime.codeTime;
                break;
            case 4:
                hours = planTime.compileTime;
                break;
            case 5:
                hours = planTime.testTime;
                break;
            case 6:
                hours = planTime.postMortemTime;
                break;
            default:
                break;
        }

        cumulativeHours += hours;
		currentDisplay->plannedHours = hours;
		currentDisplay->cumulativeHours = cumulativeHours;
        
        plannedValue = hours / planTime.totalTime;
        cumulativePlannedValue += plannedValue;
        currentDisplay->plannedValue = plannedValue;
        currentDisplay->cumulativePlannedValue = cumulativePlannedValue;
		
        currentDisplay->actualEarnedValue = plannedValue;
        currentDisplay->actualCumulativeValue = cumulativePlannedValue;
        
		utilFormatDate(currentEntry.datePlanned, &(currentDisplay->plannedDate));
		if (currentEntry.dateAccomplished > 0) {
            utilFormatDate(currentEntry.dateAccomplished, &(currentDisplay->actualDate));
        }
        else {
            strcpy(currentDisplay->actualDate, "(not yet done)");
        }
	}
        
	*printedEntries = displays;
    
	return numOfEntries;
}

void imTptDisplayTaskPlan (MYSQL* conn, Project project) {
	//display the task plan template
    CLEARSCR;
	TaskPlanEntry* entries = NULL;
	unsigned long numOfEntries = tptGetTaskPlanEntriesFromProject(conn, project, &entries);
	if (entries == NULL) {
		utilPrintError(conn, "The task plan cannot be displayed.");
		return;
	}
    
	TaskPlanDisplay* displays = NULL;
	numOfEntries = imTptConvertTaskPlanDisplays(conn, project, entries, numOfEntries, &displays);
    
	//table headers
	printf("Task Number\t");
	printf("Task Name\t");
	printf("Planned Hours\t");
	printf("Planned Value\t");
	printf("Cumulative Hours\t");
	printf("Cumulative Planned Value\t");
	printf("Planned Date\t");
	printf("Actual Date\t");
	printf("Actual Earned Value\t");
	printf("Actual Cumulative Value\t");
	printf("\n");
	
	int displayCtr;
	for (displayCtr = 0; displayCtr < numOfEntries; displayCtr++) {
		TaskPlanDisplay curDisplay = *(displays + displayCtr);
		printf("%d\t", curDisplay.number);
		printf("%s\t", curDisplay.name);
		printf("%.1f\t", curDisplay.plannedHours);
		printf("%.1f\t", curDisplay.plannedValue);
		printf("%.1f\t", curDisplay.cumulativeHours);
		printf("%.1f\t", curDisplay.cumulativePlannedValue);
		printf("%s\t", curDisplay.plannedDate);
		printf("%s\t", curDisplay.actualDate);
		printf("%.1f\t", curDisplay.actualEarnedValue);
		printf("%.1f\t", curDisplay.actualCumulativeValue);
        printf("\n");
	}
    
    printf("\n");
    
    printf("Enter any key to continue...");
    utilFlush();
    getch();
}

//determines the number of days in a month given the month and year (for leap years)
unsigned int imDetermineNumberOfDaysFromMonthAndYear (int month, int year) {
    int upperBoundDay;
    
    if (month == 0 || month == 2 || month == 4 || month == 6 || month == 7 || month == 9 || month == 11) {
		upperBoundDay = 31;
	}
	else if (month == 1) {
		if (year % 4 == 0) {
			upperBoundDay = 29;
		}
		else {
			upperBoundDay = 28;
		}
	}
	else {
		upperBoundDay = 30;
	}
    
    return upperBoundDay;
}

time_t imTptSelectMonthAndDay (TaskPlanEntry* previousEntry) {
	struct calendarDate calDate[12];
	int curMonth;
	int curYear;
    int curDay;
    
    time_t curTime = utilGetCurrentCalendarDate(&curMonth, &curDay, &curYear);
    curYear += YEAR_STANDARD;
    
    time_t previousTime = 0;
    struct tm* previousDate = NULL;
    int previousYear = 0;
    int previousMonth = 0;
    int previousDay = 0;
    if (previousEntry != NULL) {
        previousTime = previousEntry->datePlanned;
        previousDate = localtime(&previousTime);
        previousYear = previousDate->tm_year + YEAR_STANDARD;
        previousMonth = previousDate->tm_mon;
        previousDay = previousDate->tm_mday;
    }
    
    int isCurrentTimeBasis;
    if (curTime > previousTime) {
        //current time takes preecedence
        isCurrentTimeBasis = 1;
    }
    else {
        isCurrentTimeBasis = 0;
    }
    
	char* monthChoices[12];
	int monthChoicesCtr;
	for (monthChoicesCtr = 0; monthChoicesCtr < 12; monthChoicesCtr++) {
        int adjustedMonth;
        if (isCurrentTimeBasis) {
            adjustedMonth = (monthChoicesCtr + curMonth) % 12;
        }
        else {
            adjustedMonth = (monthChoicesCtr + previousMonth) % 12;
        }
		unsigned long monthStringLength = strlen(MONTHS[adjustedMonth]);
        unsigned long stringSize = monthStringLength + 6;
		monthChoices[monthChoicesCtr] = malloc(sizeof(char)* stringSize);
		
        int yearDisplay = curYear;
        
		if (adjustedMonth < curMonth) {
			yearDisplay++;
		}
		struct calendarDate* curCalDate = &(calDate[monthChoicesCtr]);
		curCalDate->month = adjustedMonth;
		curCalDate->year = yearDisplay;
		snprintf(monthChoices[monthChoicesCtr], stringSize, "%s %d", MONTHS[adjustedMonth], yearDisplay);
	}
    
    int choice = imSelectFromChoices(monthChoices, 12, "Please select a month and year.");
	struct calendarDate pickedCalDate = calDate[choice - 1];
    int month = pickedCalDate.month;
    
    int day = 0;
	unsigned int upperBoundDay = 0;
	unsigned int lowerBoundDay = 1;
    
	upperBoundDay = imDetermineNumberOfDaysFromMonthAndYear(month, pickedCalDate.year);
    
    if (isCurrentTimeBasis && month == curMonth) {
        lowerBoundDay = curDay;
    }
    else if (!isCurrentTimeBasis && month == previousMonth) {
        lowerBoundDay = previousDay;
    }
    else {
        lowerBoundDay = 1;
    }
    
    const unsigned int MESSAGE_STRING_SIZE = 50;
    char* message = malloc(sizeof(char) * MESSAGE_STRING_SIZE);
    snprintf(message, MESSAGE_STRING_SIZE, "Please enter a day between %d and %d", lowerBoundDay, upperBoundDay);
    
    day = imInputNumberRange(lowerBoundDay, upperBoundDay, message, "You did not enter a valid day!");
    
	struct tm* curTimeStruct = localtime(&curTime);
	curTimeStruct->tm_mon = pickedCalDate.month;
	curTimeStruct->tm_mday = day;
	curTimeStruct->tm_year = pickedCalDate.year - YEAR_STANDARD;
    
    time_t curNanos = mktime(curTimeStruct);
    
	//free months
	int monthFreeCtr;
	for (monthFreeCtr = 0; monthFreeCtr < 12; monthFreeCtr++) {
		free(monthChoices[monthFreeCtr]);
	}
    
	return curNanos;
}

void imTptFillUpPhases (MYSQL* conn, TaskPlan plan) {
	//ask for user input (planned date of completion) before inserting a task plan entry record
    CLEARSCR;
	Phase* phases = NULL;
	unsigned long numOfPhases = priGetPhases(conn, &phases);
	
	if (phases == NULL) {
		utilPrintError(conn, "SQL connection may have failed.");
		return;
	}
    
	int phaseCtr;
	TaskPlanEntry* entries = malloc(sizeof(TaskPlanEntry) * numOfPhases);
    TaskPlanEntry* previousEntry = NULL;
	
	for (phaseCtr = 0; phaseCtr < numOfPhases; phaseCtr++) {
		//detect whether a task plan entry for the specified phase for the current plan exists
        Phase currentPhase = *(phases + phaseCtr);
		int newEntry = 1;
		TaskPlanEntry* existingEntry;
		if ((existingEntry = tptGetProjectTaskPlanEntryWithPhase(conn, plan, currentPhase)) != NULL) {
			newEntry = 0;
		}
        
		TaskPlanEntry* currentEntry;
		if (newEntry) {
			currentEntry = entries + phaseCtr;
		}
		else {
			currentEntry = existingEntry;
		}
		
		printf("What date do you plan on finishing the %s phase?\n", currentPhase.phaseName);
		time_t monthAndDay = imTptSelectMonthAndDay(previousEntry);
		currentEntry->datePlanned = monthAndDay;
		currentEntry->phaseId = currentPhase.phaseId;
		currentEntry->taskPlanId = plan.taskPlanId;
		currentEntry->dateAccomplished = 0;
        
        if (previousEntry != NULL) {
            free(previousEntry);
        }
        
        previousEntry = malloc(sizeof(TaskPlanEntry));
        *previousEntry = *currentEntry;
        
		if (!newEntry) {
			tptUpdateTaskPlanEntry(conn, *currentEntry);
			free (currentEntry);
		}
		else {
			tptInsertTaskPlanEntry(conn, *currentEntry);
		}
	}
    
	free(entries);
}

/**DEFECT RECORDING LOG UI**/

DefectType* imDrlChooseCategory (MYSQL* conn, int cancelEnabled) {
    //list all the defect categories and ask the user to pick one
    DefectType* defectTypes = NULL;
    unsigned long numOfTypes = drlGetDefectCategories(conn, &defectTypes);
    unsigned long numberOfChoices = numOfTypes;
    
    if (cancelEnabled) {
        numberOfChoices++;
    }
    
    char* choices[numberOfChoices];
    int typeCtr;
    for (typeCtr = 0; typeCtr < numOfTypes; typeCtr++) {
        DefectType curType = *(defectTypes + typeCtr);
        unsigned long stringSize = strlen(curType.typeName) + 1 + strlen(curType.typeDesc) + 1;
        choices[typeCtr] = malloc(sizeof(char) * stringSize);
        snprintf(choices[typeCtr], stringSize, "%s\n%s", curType.typeName, curType.typeDesc);
    }
    
    if (cancelEnabled) {
        char* backString = "Back";
        choices[numOfTypes] = malloc(sizeof(char) * (strlen(backString) + 1));
        strcpy(choices[numOfTypes], backString);
    }
    
    int typeChoice = imSelectFromChoices(choices, (unsigned int)numberOfChoices, "Please select a defect category.");
    
    if (cancelEnabled && typeChoice == numOfTypes + 1) {
        return NULL;
    }
    else {
        DefectType* defectToRet = malloc(sizeof(DefectType));
        *defectToRet = *(defectTypes + (typeChoice - 1));
        free(defectTypes);
        return defectToRet;
    }
}

void imDrlPauseFixingDefect (MYSQL* conn, Project project, int confirmEnabled) {
    Defect* defects = NULL;
    //get defect that has started fixing
    unsigned long numOfDefectsBeingFixed = drlGetDefectsBeingFixed(conn, project, &defects);
    if (numOfDefectsBeingFixed == 0) {
        printf("No defects being fixed right now.\n");
    }
    else if (numOfDefectsBeingFixed > 1) {
        printf("Uh oh. Bug was encountered. IMPAUSEFIXINGDEFECT(): Cannot fix more than one defect at a time.\n");
    }
    else {
        if (confirmEnabled == 1) {
            //assume only one defect
            char* defectInfo = NULL;
            drlGetDefectInformationString(conn, *defects, &defectInfo);
            printf("The timer for fixing this defect will be paused:\n");
            printf("%s\n\n", defectInfo);
            int choice = imSelectYesOrNo("Are you sure?");
            
            if (choice == 1) {
                drlPauseFixDefect(conn, *defects);
            }
        }
        else {
            drlPauseFixDefect(conn, *defects);
        }
    }
}

int imDrlSelectDefect (MYSQL* conn, Defect* defects, unsigned long numOfDefects, int cancelEnabled, char* optionalMessage) {
    unsigned long choiceSize = numOfDefects;
    if (cancelEnabled) {
        choiceSize++;
    }
    char* defectChoices [choiceSize];
    
    int defectCtr = 0;
    for (; defectCtr < numOfDefects; defectCtr++) {
        Defect curDefect = *(defects + defectCtr);
        char* defectString = NULL;
        unsigned int strSize = drlGetDefectInformationString(conn, curDefect, &defectString);
        defectChoices[defectCtr] = malloc(sizeof(char) * (strSize + 1));
        strcpy(defectChoices[defectCtr], defectString);
        free(defectString);
    }
    
    if (cancelEnabled) {
        char* backString = "Cancel";
        defectChoices[numOfDefects] = malloc(sizeof(char) * (strlen(backString) + 1));
        strcpy(defectChoices[numOfDefects], backString);
    }
    
    return imSelectFromChoices(defectChoices, choiceSize, optionalMessage);
}

void imDrlInjectDefectDependency (MYSQL* conn, unsigned long long defectDependentId) {
    DefectDependency* dependency = malloc(sizeof(DefectDependency));
    dependency->defectCreatedId = (int)defectDependentId;
    
    //ask to select the defect that the defect is dependent on
    char query[300];
    Defect* defects = NULL;
    snprintf(query, 300, "SELECT * FROM %s WHERE %s <> %llu;", DefectTable.tableName, DefectTable.defectId, defectDependentId);
    unsigned long numOfDefects = drlDefectExecuteQuery(conn, query, &defects);
    
    if (numOfDefects != 0) {
        int dependencyChoice = imSelectYesOrNo("Is the defect created by or dependent on another defect?");
        if (dependencyChoice == 1) {
            //yes ask dependency
            int choice = imDrlSelectDefect(conn, defects, numOfDefects, 1, "Select a defect that the new defect is dependent on.");
            
            if (choice != numOfDefects + 1) {
                dependency->defectOriginalId = defects[choice - 1].defectId;
                drlInsertDefectDependency(conn, *dependency);
            }
            
            free(defects);
        }
    }
    
    free(dependency);
}

void imDrlInjectDefect (MYSQL* conn, Project project, Phase curPhase) {
    Phase* injectPhase = NULL;
    char question[200];
    snprintf(question, 200, "Was the defect injected at the %s phase?", curPhase.phaseName);
    
    while (injectPhase == NULL) {
        int choice = imSelectYesOrNo(question);
        if (choice == 1) {
            //yes
            injectPhase = malloc(sizeof(Phase));
            *injectPhase = curPhase;
        }
        else {
            //no
            injectPhase = imPriPhaseSelect(conn, 0);
        }
    }
    
    printf("\n");
    DefectType* type = NULL;
    while (type == NULL) {
        type = imDrlChooseCategory(conn, 0);
    }

    printf("\n");
    printf("Please supply a description of the defect: ");
//    char bufferChar;
//    scanf("%c", &bufferChar);
    utilFlush();
    char description[1000];
    fgets(description, 1000, stdin);
    char* lastChar = description + strlen(description) - 1;
    if (*lastChar == '\n') {
        *lastChar = '\0';
    }
    
    drlInjectDefect(conn, project, *injectPhase, description, *type);
    
    imDrlInjectDefectDependency(conn, mysql_insert_id(conn));
    
}

void imDrlStartFixingDefect (MYSQL* conn, Project project, Phase curPhase) {
    //check if there are other defects being fixed
    Defect* defectsBeingFixed = NULL;
    unsigned long numberOfDefectsBeingFixed = drlGetDefectsBeingFixed(conn, project, &defectsBeingFixed);
    free(defectsBeingFixed);
    if (numberOfDefectsBeingFixed != 0) {
        printf("A fix is already in progress! Fix or pause the defect being fixed first.\n");
        utilFlush();
        return;
    }
    
    Defect* defects = NULL;
    //get defects
    unsigned long numOfDefects = drlGetUnfixedDefects(conn, project, &defects);
    
    if (numOfDefects == 0) {
        printf("There are no defects that you can start fixing.");
        return;
    }
    
    int choice = imDrlSelectDefect(conn, defects, numOfDefects, 1, "Select a defect to fix. Any paused fixes are continued.");
    
    if (choice == numOfDefects + 1) {
        free(defects);
        return;
    }
    else {
        Defect selectedDefect = *(defects + (choice - 1));
        
        if (selectedDefect.startFixTime == 0) {
            //hasn't started fixing yet...
            drlStartFixDefect(conn, curPhase, selectedDefect);
        }
        else {
            //since there is a check whether any defects are being worked on,
            //no need to look for unended fix pause records
            //i.e., even with pause records, if the defect has already continued
            //fixing, then the first check should have already caught that
            drlUnpauseFixDefect(conn, selectedDefect);
        }
        free(defects);
    }
}

int imDrlNoDefectOptions (MYSQL* conn, Project project, Phase curPhase) {
    //1 - Inject defect, 2 - back
    printf("There are no defects.\n\n");
    char* choices [2] = {"Inject defect", "Back"};
    int choice = imSelectFromChoices(choices, 2, "Select an option");
    
    if (choice == 1) {
        //inject defect
        imDrlInjectDefect(conn, project, curPhase);
    }
    else {
        return 0;
    }
    
    return 1;
}

void imDrlFinishFixingDefect (MYSQL* conn, Project project, Phase curPhase) {
    Defect* defects = NULL;
    //get defect that has started fixing (and not paused)
    unsigned long numOfDefectsBeingFixed = drlGetDefectsBeingFixed(conn, project, &defects);
    if (numOfDefectsBeingFixed == 0) {
        printf("No defects in progress right now. Try starting a defect right up; you may have paused one too.\n");
    }
    else if (numOfDefectsBeingFixed > 1) {
        printf("Uh oh. Bug was encountered. Cannot fix more than one defect at a time.\n");
    }
    else {
        //assume only one defect
        char* defectInfo = NULL;
        drlGetDefectInformationString(conn, *defects, &defectInfo);
        printf("Please confirm that you're finished fixing this defect:\n");
        printf("%s\n\n", defectInfo);
        int choice = imSelectYesOrNo("Are you sure?");
        
        if (choice == 1) {
            drlFinishFixDefect(conn, curPhase, *defects);
        }
    }
}

int imDrlHasDefectOptions (MYSQL* conn, Project project, Phase curPhase) {
    //1 - Inject defect, 2 - Start fixing defect, 3 - Pause fixing defect, 4 - Finish fixing defect, 5 - back
    drlDisplayDefectLog(conn, project);
    
    char* choices [5] = {"Inject defect", "Start fixing defect", "Pause fixing defect", "Finish fixing defect", "Back"};
    int choice = imSelectFromChoices(choices, 5, "Select an option");
    
    if (choice == 1) {
        //inject defect
        imDrlInjectDefect(conn, project, curPhase);
    }
    else if (choice == 2) {
        //start fixing defect shows a list of defects that have yet to be fixed (and is not paused)
        imDrlStartFixingDefect(conn, project, curPhase);
    }
    else if (choice == 3) {
        //pause fixing defect shows a list of defects whose fix time has started and is not paused
        imDrlPauseFixingDefect(conn, project, 1);
    }
    else if (choice == 4) {
        //finish fixing defect shows a list of defects whose fix times are not paused but have started
        imDrlFinishFixingDefect(conn, project, curPhase);
    }
    else {
        return 0;
    }
    
    return 1;
}

void imDrlViewDefectOptions (MYSQL* conn, Project project, Phase curPhase) {
    int refresh = 1;
    while (refresh == 1) {
        CLEARSCR;
        Defect * defects = NULL;
        unsigned long numOfDefects =                                                                                                                                                                                                          drlGetDefectsForProject(conn, project, &defects);
        
        if (numOfDefects == 0) {
            refresh = imDrlNoDefectOptions(conn, project, curPhase);
        }
        else {
            refresh = imDrlHasDefectOptions(conn, project, curPhase);
        }
        
        free(defects);
    }
}

void imDrlDisplayCategories (MYSQL* conn) {
    DefectType* categories;
    unsigned long numOfCategories = drlGetDefectCategories(conn, &categories);
    
    //display category information
    int categoryCtr;
    printf("TYPE NAME - TYPE DESCRIPTION");
    for (categoryCtr = 0; categoryCtr < numOfCategories; categoryCtr++) {
        DefectType curCategory = *(categories + categoryCtr);
        printf("%s - %s", curCategory.typeName, curCategory.typeDesc);
    }
}

/**TIME RECORDING LOG UI**/

int imTrlShowInterruptOptions (MYSQL* conn, Project project, Phase phase) {
	//display option to stop interrupting
//	char* choices[] = {"Continue phase", "Back"};
//
//	int choice = imSelectFromChoices(choices, 2, "Select from the choices");
//
//	if (choice == 1) {
		//would you like to state a reason for the interruption?
		int reasonChoice = imSelectYesOrNo("Would you like to state a reason for the interruption?");
		char* reason = NULL;

		if (reasonChoice == 1) {
            reason = malloc(sizeof(char) * 1000);
			printf("State a reason for the interruption (999 chars. max): ");
//			scanf("%c", reason);
            utilFlush();
            fgets(reason, 1000, stdin);
            char* newLineChar = strchr(reason, '\n');
            *newLineChar = '\0';
//            printf("Reason: %s\n", reason);
		}

		trlStopInterruption(conn, project, phase, reason);
        
        //would you like to fix a defect?
        Defect* defects = NULL;
        unsigned long numOfDefects = drlGetUnfixedDefects(conn, project, &defects);
        free(defects);
        
        if (numOfDefects > 0) {
            int defectFixChoice = imSelectYesOrNo("Would you like to fix a defect? (When you interrupt your work, the timer for the defect fix time is also paused.)");
            if (defectFixChoice == 1) {
                //ask the user to select a defect to continue fixing
                imDrlStartFixingDefect(conn, project, phase);
            }
        }
		return 1;
//	}
//	else {
//		return 0;
//	}

}

int imTrlStartLoggingOptions (MYSQL* conn, Project project, Phase phase) {
	//display options to start logging
//	char* choices[] = {"Start phase", "Back"};
//
//	int choice = imSelectFromChoices(choices, 2, "Select from the choices");
//
//	if (choice == 1) {
		//start logging
		trlStartRecord(conn, project, phase);
		return 1;
//	}
//	else {
//		return 0;
//	}
}

int imTrlStopLoggingOptions (MYSQL* conn, Project project, Phase phase, int phaseDone) {
    if (trlIsPhaseFinished(conn, project, phase)) {
        printf("This phase is finished. Press any key to continue.");
        utilFlush();
        getch();
        
        return 0;
    }
    
    int numOfChoices;
    
    if (phaseDone) {
        numOfChoices = 3;
    }
    else {
        numOfChoices = 2;
    }
    
	//display options to stop logging (work ongoing)
	char* choices[numOfChoices];
    int choiceCtr = 0;
    int finishChoice = 0, interruptChoice = 0, manageDefectsChoice = 0;
    
    if (numOfChoices == 3) {
        char* finishPhaseString = "Finish phase";
        choices[choiceCtr] = malloc(sizeof(char) * (strlen(finishPhaseString) + 1));
        strcpy(choices[choiceCtr], finishPhaseString);
        choiceCtr++;
        finishChoice = choiceCtr;
    }
    
    char* interruptString = "Interrupt phase";
    choices[choiceCtr] = malloc(sizeof(char) * (strlen(interruptString) + 1));
    strcpy(choices[choiceCtr], interruptString);
    choiceCtr++;
    interruptChoice = choiceCtr;
    
    char* manageDefectsString = "Manage defects";
    choices[choiceCtr] = malloc(sizeof(char) * (strlen(manageDefectsString) + 1));
    strcpy(choices[choiceCtr], manageDefectsString);
    choiceCtr++;
    manageDefectsChoice = choiceCtr;
    
    int choice = imSelectFromChoices(choices, numOfChoices, "Select from the choices");

	if (finishChoice != 0 && choice == finishChoice) {
		//stop logging
		trlStopRecord(conn, project, phase);
		return 0;
	}
	else if (choice == interruptChoice) {
		//interrupt
		trlStartInterruption(conn, project, phase);
        
        //pause any defects being fixed
        imDrlPauseFixingDefect(conn, project, 0);
		return 0;
	}
    else if (choice == manageDefectsChoice) {
        //manage defects
        imDrlViewDefectOptions (conn, project, phase);
        return 1;
    }
	else {
		return 0;
	}
}

void imTrlRecordTime (MYSQL* conn, Project project, Phase phase) {
	int interrupted = 0;
	int refresh = 1;
    int phaseFinished = 0;

	while (refresh == 1) {
        CLEARSCR;
        //check if the phase being logged is interrupted
        interrupted = trlIsPhaseInterrupted(conn, project, phase);
		
		printf("OPTIONS FOR %s\n\n", phase.phaseName);

		if (interrupted) {
            refresh = imTrlShowInterruptOptions(conn, project, phase);
//            if (refresh) {
//                phaseFinished = imViewPhaseSpecificOptions(conn, project, phase);
//            }
		}
		else {
			if (trlGetTimeLogForPhase(conn, project, phase) == NULL) {
				refresh = imTrlStartLoggingOptions(conn, project, phase);
//                if (refresh) {
//                    phaseFinished = imViewPhaseSpecificOptions(conn, project, phase);
//                }
			}
			else {
				phaseFinished = imViewPhaseSpecificOptions(conn, project, phase);
                CLEARSCR;
                printf("OPTIONS FOR %s\n\n", phase.phaseName);
                refresh = imTrlStopLoggingOptions(conn, project, phase, phaseFinished);
			}
		}
	}
}

/**GENERAL UI**/
//returns whether a phase is finished (1) or not (0)
int imViewPhaseSpecificOptions(MYSQL* conn, Project project, Phase phase) {
    //started logging, view phase specific options
    int phaseId = phase.phaseId;
    
    const unsigned int PLANNING = 1;
    const unsigned int TESTING = 5;
    const unsigned int POSTMORTEM = 6;
    
    switch (phaseId) {
        case PLANNING:
            return imPlanningOptions(conn, project, phase);
            break;
        case TESTING:
            imTrtMenu(conn, project, phase);
            //check if defects persist
            Defect* defects = NULL;
            if (drlGetUnfixedDefects(conn, project, &defects) == 0) {
                free(defects);
                return 1;
            }
            else {
                free(defects);
                printf("You cannot finish testing without fixing all defects.\n\n");
                return 0;
            }
            return 1;
            break;
        case POSTMORTEM:
            imPipMenu(conn, project.userProjectId);
        default:
            return 1;
            break;
    }
    
}

//Returns whether the planning phase is done (1) or not (0)
int imPlanningOptions(MYSQL* conn, Project project, Phase phase) {
    int choice, numOfChoices, finished = 0;
    
    do {
        int plannedTimeChoice = 0, sizeEstimateChoice = 0, taskPlanningChoice = 0, manageDefectsChoice = 0;
        numOfChoices = 1;
        
        //check if pps has been filled out
        PlannedTime planTime;
        planTime.planId = ppsGetPlanIDFromProject(conn, project);
        if (!ppsPlannedTimeCheck(conn, planTime)) {
            if (!ppsHasPlanForProject(conn, project)) {
                ppsSetProjectPlan(conn, project);
            }
            plannedTimeChoice = numOfChoices;
//            printf("%d\n", plannedTimeChoice);
            numOfChoices++;
        }
        
        //check if size estimating template has been filled out
        if (project.pspId >= 4 && !stHasSizeEstimate(conn, project)) {
            sizeEstimateChoice = numOfChoices;
//            printf("%d\n", sizeEstimateChoice);
            numOfChoices++;
        }
        
        //check if task planning template has been filled out
        if (project.pspId >= 5 && tptGetTaskPlanForProject(conn, project) == NULL) {
            taskPlanningChoice = numOfChoices;
//            printf("%d\n", taskPlanningChoice);
            numOfChoices++;
        }
        
        manageDefectsChoice = numOfChoices;
//        printf("%d\n", manageDefectsChoice);
        numOfChoices++;
        
        CLEARSCR;
        if (numOfChoices == 2) {
            //no more forms
            finished = 1;
            printf("There are no more forms to be filled.\n\n");
            break;
        }
        else {
            printf("Please fill up the forms one by one.\n\n");
        }
        char* choices[numOfChoices];
        
        if (plannedTimeChoice != 0) {
            char* projectPlanSummaryString = "Project Plan Summary";
            choices[plannedTimeChoice - 1] = malloc(sizeof(char) * (strlen(projectPlanSummaryString) + 1));
            strcpy(choices[plannedTimeChoice - 1], projectPlanSummaryString);
        }
        if (sizeEstimateChoice != 0) {
            char* sizeEstimateString = "Size Estimating Template";
            choices[sizeEstimateChoice - 1] = malloc(sizeof(char) * (strlen(sizeEstimateString) + 1));
            strcpy(choices[sizeEstimateChoice - 1], sizeEstimateString);
        }
        if (taskPlanningChoice != 0) {
            char* taskPlanningString = "Task Planning Template";
            choices[taskPlanningChoice - 1] = malloc(sizeof(char) * (strlen(taskPlanningString) + 1));
            strcpy(choices[taskPlanningChoice - 1], taskPlanningString);
        }
        
        //manage defects
        char* manageDefectsString = "Manage defects";
        choices[numOfChoices - 2] = malloc(sizeof(char) * (strlen(manageDefectsString) + 1));
        strcpy(choices[manageDefectsChoice - 1], manageDefectsString);
        
        //back
        //printf("%d\n", numOfChoices);
        char* backString = "Back";
        choices[numOfChoices - 1] = malloc(sizeof(char) * (strlen(backString) + 1));
        strcpy(choices[numOfChoices - 1], backString);
        
        choice = imSelectFromChoices(choices, numOfChoices, "Please select the number of the form");
        
        TaskPlan* plan;
        
        if (choice == plannedTimeChoice) {
            //go to project plan summary
            ppsSetPlannedTime(conn, project);
        }
        else if (choice == sizeEstimateChoice) {
            imStMain(conn, project);
        }
        else if (choice == taskPlanningChoice) {
            if (!tptCheckTaskPlanningTemplate(conn, project)) {
                plan = tptCreateTaskPlan(conn, project);
                imTptFillUpPhases(conn, *plan);
            }
        }
        else if (choice == manageDefectsChoice) {
            //manage defects
            imDrlViewDefectOptions(conn, project, phase);
        }
    } while (choice != numOfChoices);
    
    if (finished) {
        //all done
        return 1;
    }
    else {
        //pause phase
        return 0;
    }
}

void imViewForms(MYSQL* conn, Project project){
	int choice;
	do{
		//LIST OF PROJECTS WHICH IS DONE ALREADY...
		//CHOOSE FROM LIST before the following:
		CLEARSCR;
        int ctr = 1, pspId = project.pspId;
        int ppsChoice = 0, setChoice = 0, tptChoice = 0, sptChoice = 0, trtChoice = 0, pipChoice = 0;
        
		printf("PSP Forms:\n\n");
		printf("[%d] Project Plan Summary\n", ctr);
        ppsChoice = ctr++;
        
        if (pspId >= 4) {
            printf("[%d] Size Estimating Template\n", ctr);
            setChoice = ctr++;
        }
        if (pspId >= 5) {
            printf("[%d] Task Planning Template\n", ctr);
            tptChoice = ctr++;
            printf("[%d] Schedule Planning Template\n", ctr);
            sptChoice = ctr++;
        }
		printf("[%d] Test Report Template\n", ctr);
        trtChoice = ctr++;
		printf("[%d] Process Improvement Plan\n\n", ctr);
        pipChoice = ctr++;
		printf("[0] Go Back\n\n");
		printf("Which form do you want to view? ");
		scanf("%d", &choice);
		
        if (choice == 0) {
            
        }
		else if(choice == ppsChoice){
			//VIEW PROJECT PLAN SUMMARY ALLEN
            imPpsDisplayProjectPlanSummary(conn, project);
		}
		else if(choice == setChoice){
			//VIEW SIZE ESTIMATING TEMPLATE ALLEN
            imStViewSizeEstimatingTemplate(conn, project);
		}
		else if(choice == tptChoice){
			//VIEW TASK PLANNING TEMPLATE KEVIN
            imTptDisplayTaskPlan(conn, project);
		}
		else if(choice == sptChoice){
			//VIEW SCHEDULE PLANNING TEMPLATE ALLEN
		}
		else if(choice == trtChoice){
			//VIEW TESTREPORT TEMPLATE BERNA - imTrtDisplayAllReports(conn, userProjectId);
            imTrtDisplayAllReports(conn, project.userProjectId);
		}
		else if(choice == pipChoice){
			//VIEW PROCESS IMPROVEMENT PLAN BERNA - imPipDisplayPlan(conn, procImpPlan.piplanId);
            unsigned long piplanId = pipGetPlanIdFromProject(conn, project);
            
            if (piplanId == 0) {
                printf("The project improvement plan is still unavailable.\n\n");
                utilFlush();
                getch();
            }
            else {
                imPipDisplayPlan(conn, (int)piplanId);
            }
		}
		else{
			printf("\nInvalid input, choose from the choices above only...");
			getch();
		}
	}while(choice != 0); //CHECK max choice if 6
}

/**SECTION UI**/

void imUsmSectionManagement(MYSQL* conn){
	int choice;
	
	do{
		CLEARSCR;
		printf("SECTION MANAGEMENT\n\n");
		printf("[1] Add Section\n");
		printf("[2] Edit Section\n");
		printf("[3] View Section\n\n");
		
		printf("[0] Go Back\n\n");
		
		printf("choice: ");
		scanf("%d", &choice);
		
		if(choice == 1){
			usmAddSection(conn);
		}
		else if(choice == 2){
			usmEditSection(conn);
		}
		else if(choice == 3){
			usmViewSection(conn);
		}
		else if(choice == 0){
			return;
		}
		else{
			printf("\n\nInvalid input...");
			getch();
		}
	}while(choice != 0 && choice != 1 && choice != 2 && choice != 3);
}

/**STARTING POINT**/

int imDisplayMain(){
	int choice;
    int refresh = 1;
	
    char* userName;
    char* password;
    int correct = 0;
    
    while (!correct) {
        userName = NULL;
        password = NULL;
        
        dbAskLogin(&userName, &password);
        
        MYSQL* testConn = dbConnectDatabase("localhost", userName, password, NULL, 0, NULL, 0);
        if (testConn == NULL) {
            utilPrintError(testConn, "User name and password were not correct.");
        }
        else {
            dbStoreLogin(userName, password);
            if (!dbCheckDatabaseExists(testConn, (char*)DB_NAME)) {
                //execute scripts
                int success = dbExecuteSchemaFiles(testConn);
                
                if (!success) {
                    return 1;
                }
            }
            
            mysql_close(testConn);
            correct = 1;
        }
    }
    
	MYSQL* conn = dbConnectDefaultDatabase(userName, password);
	
    free(userName);
    free(password);
    
	do{
		CLEARSCR;
		printf("|-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-|\n");
		printf("- AUTOMATED PERSONAL SOFTWARE PROCESS -\n");
		printf("|-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-:-|\n");
		printf("	[1] Login\n");
		printf("	[2] Signup\n");
		printf("	[3] Section Management\n\n");
		
		printf("	[0] Exit\n\n");
		
		printf("	choice: ");
		scanf("%d", &choice);
		
		if(choice == 1){
			usmLogin(conn);
		}
		else if(choice == 2){
			usmSignup(conn);
		}
		else if(choice == 3){
			imUsmSectionManagement(conn);
		}
		else if(choice == 0){
            refresh = 0;
		}
		else{
			printf("\n\nInvalid input...");
			getch();
		}
	} while(refresh);
	
	dbDisconnectDatabase(conn);
	utilFlush();
	return 0;
}

/**SIZE ESTIMATING TEMPLATE**/
void imStViewSizeEstimatingTemplate(MYSQL* conn, Project project) {
    PlannedLOC plannedLOC;
    plannedLOC = ppsGetPlannedLOC(conn, project);
    
    printf("Base Program\n");
    printf("=========================\n\n");
    printf("\t\tBase Size\t\t\t%.2f\n", plannedLOC.planBaseLOC);
    printf("\t\tLOC Deleted\t\t\t%.2f\n", plannedLOC.planDeletedLOC);
    printf("\t\tLOC Modified\t\t\t%.2f\n", plannedLOC.planModifiedLOC);
    
    printf("\n\n");
    
    //get size estimate
    
    
    NewObject* newObjects = NULL;
    SizeEstimate* sizeEst = stGetSizeEstimateById(conn, project);
    unsigned long numOfNewObjs = stGetNewObjectsForSizeEstimate(conn, *sizeEst, &newObjects);
    
    float newObjectsLOC = stGetTotalNewObjects(conn, *sizeEst);
    
    if (numOfNewObjs > 0) {
        printf("New Objects\n");
        printf("================================================\n\n");
        printf("Function Names\t\t\tType\t\tRelative Size\t\tLOC\n");
        
        int newObjCtr;
        for (newObjCtr = 0; newObjCtr < numOfNewObjs; newObjCtr++) {
            NewObject curObj = newObjects[newObjCtr];
            ObjectSize* objSize = stGetObjectSizeById(conn, curObj.objectsize_id);
            if (objSize == NULL) {
                continue;
            }
            else {
                SizeType* sizeType = stGetSizeTypeByTypeId(conn, objSize->sizeId);
                ObjectType* objType = stGetObjectTypeByTypeId(conn, objSize->typeId);
                
                printf("%s\t\t\t%s\t\t%s\t\t%.2f\n", curObj.function_name, objType->typeName, sizeType->sizeName, objSize->size);
            }
        }
        
        printf("\t\t\t\t\t\t\t\t\t\t%.2f\n\n\n", newObjectsLOC);
    }
    
    ReusedObject* reusedObjects = NULL;
    unsigned long numOfReusedObjs = stGetReusedObjectsForSizeEstimate(conn, *sizeEst, &reusedObjects);
    
    if (numOfReusedObjs > 0) {
        printf("Reused Objects\n");
        printf("================================================\n");
        
        int reusedObjCtr;
        for (reusedObjCtr = 0; reusedObjCtr < numOfReusedObjs; reusedObjCtr++) {
            ReusedObject reusedObj = reusedObjects[reusedObjCtr];
            printf("%s\t\t\t%.2f", reusedObj.function_name, reusedObj.function_lines);
        }
        
        printf("\n\t\t\t\t%d\n\n", stGetReusedTotal(conn, *sizeEst));
    }
    
    printf("Projected LOC:\t\t\t%.2f\n\n", newObjectsLOC);
    
    printf("Press any key to continue...");
    utilFlush();
    getch();
}

void imStMain(MYSQL* conn, Project project)
{
    BaseProgram baseProgram;
    NewObject* newObject = NULL;
    ReusedObject* reusedObject = NULL;
    SizeEstimate sizeEstimate;
    
    int sizeEstimatePlanId = (int)stSetSizeEstimate(conn, project);
    sizeEstimate.user_project_id = sizeEstimatePlanId;
    sizeEstimate.user_project_id = project.userProjectId;
    
    int next = 1;
    int counter = 0;
    int type, size;
    int key;//, rest;
    
    printf("Base Program\n");
    printf("  Base Size (B): ");
    scanf("%d", &baseProgram.baseSize);
    printf("  LOC Deleted (D): ");
    scanf("%d", &baseProgram.LOCDeleted);
    printf("  LOC Modified (M): ");
    scanf("%d", &baseProgram.LOCModified);
    
    if(baseProgram.baseSize > 0)
    {
        printf("\nProject LOC (P)\n");
        printf("Total Base Additions: 0\n");
    }
    
    printf("\nNew Objects (NO)");
    
    while(next == 1)
    {
        if (newObject == NULL) {
            newObject = malloc(sizeof(NewObject));
        }
        else {
            NewObject* realloced = realloc(newObject, sizeof(NewObject) * (counter + 1));
            
            if (realloced == NULL) {
                newObject = realloced;
            }
        }
        
        newObject[counter].template_id = sizeEstimate.template_id;
        
        utilFlush();
        printf("\nFunction name: ");
        scanf("%s", newObject[counter].function_name);
        printf("Type (enter 0 for list): ");
        scanf("%d", &type);
        utilFlush();
        
        if(type == 0)
        {
            printf("  1 - Calculation\n");
            printf("  2 - Data\n");
            printf("  3 - I/O\n");
            printf("  4 - Logic\n");
            printf("  5 - Set-up\n");
            printf("  6 - Text\n");
            
            printf("Type: ");
            scanf("%d", &type);
        }
        
        printf("Size (enter 0 for list): ");
        scanf("%d", &size);
        
        if(size == 0)
        {
            printf("  1 - Very Small\n");
            printf("  2 - Small\n");
            printf("  3 - Medium\n");
            printf("  4 - Large\n");
            printf("  5 - Very Large\n");
            
            printf("Size: ");
            scanf("%d", &size);
        }
        
        if(stSetNewObject(conn, newObject[counter], type, size) == 0)    // if input in db properly, counter increases for the next. Else, it doesn't so you can try to input again.
        {
            counter++;
            utilFlush();
            printf("Press ANY to continue, or BACKSPACE to finish.\n");
            key = getch();
            
            if(key == BACKSPACE)
            {
                next = 0;
            }
        }
        else
        {
            printf("ERROR - Data wasn't saved into DB. Try again.\n");
        }
    }
    
    next = 1;
    counter = 0;
    printf("\nReused Objects");
    
    while(next == 1)
    {
        if (reusedObject == NULL) {
            reusedObject = malloc(sizeof(ReusedObject));
        }
        else {
            ReusedObject* realloced = realloc(reusedObject, sizeof(ReusedObject) * (counter + 1));
            
            reusedObject = realloced;
        }
        
        reusedObject[counter].template_id = sizeEstimate.template_id;
        
        utilFlush();
        printf("\nFunction name: ");
        scanf("%s", reusedObject[counter].function_name);
        printf("Function lines: ");
        scanf("%f", &reusedObject[counter].function_lines);
        
        if(stSetReusedObject(conn, reusedObject[counter]) == 0)    // if input in db properly, counter increases for the next. Else, it doesn't so you can try to input again.
        {
            counter++;
            utilFlush();
            printf("Press ANY to continue, or BACKSPACE to finish.\n");
            key = getch();
            
            if(key == BACKSPACE)
            {
                next = 0;
            }
        }
        else
        {
            printf("ERROR - Data wasn't saved into DB. Try again.\n");
        }
    }
    
    float b0, b1, projectedLOC, estimatedNewAndChangedLOC, estimatedTotalLOC;
    
    projectedLOC = stGetTotalNewObjects(conn, sizeEstimate);
    
    printf("\nProjected LOC (P): %f\n", projectedLOC);
    printf("Regression Parameter B0: ");
    scanf("%f", &b0);
    printf("Regression Parameter B1: ");
    scanf("%f", &b1);
    estimatedNewAndChangedLOC = b0+b1*(projectedLOC+baseProgram.LOCModified);
    printf("Estimated New and Changed LOC (N): %f\n", estimatedNewAndChangedLOC); //should also be inserted in the DB, kase kinukuha to ng PPS.
    estimatedTotalLOC = estimatedNewAndChangedLOC + baseProgram.baseSize - baseProgram.LOCDeleted - baseProgram.LOCModified + stGetReusedTotal(conn, sizeEstimate);
    printf("Estimated Total LOC (T): %f\n", estimatedTotalLOC);
}

/**TEST REPORT TEMPLATE**/

void imTrtDisplayReport(MYSQL* conn, int userProjectId, int testNum){
	string300 query;
	
	printf("Test Number: %d\n", testNum);
	printf("Test Objective: ");
	trtConcatString(query, 1, userProjectId, testNum);
	trtSelectDetails(conn, query);
	printf("Test Description: ");
	trtConcatString(query, 2, userProjectId, testNum);
	trtSelectDetails(conn, query);
	printf("Expected Results: ");
	trtConcatString(query, 3, userProjectId, testNum);
	trtSelectDetails(conn, query);
	printf("Actual Results: ");
	trtConcatString(query, 4, userProjectId, testNum);
	trtSelectDetails(conn, query);
}

void imTrtDisplayAllReports(MYSQL* conn, int userProjectId){
	string300 queryCount;
	trtConcatString(queryCount, 6, userProjectId, 0);
	int entryCount = trtCountDetails(conn, queryCount);
	
	CLEARSCR;
	printf("Test Reports\n\n");
	int i;
	for(i = 1; i < entryCount + 1; i++){
		imTrtDisplayReport(conn, userProjectId, i);
		printf("\n");
	}
    utilFlush();
	getch();
}

void imTrtGetInput(MYSQL* conn, int userProjectId){
	TestReport testReport;
	string300 queryCount;
	trtConcatString(queryCount, 6, userProjectId, 0);
	int entryCount = trtCountDetails(conn, queryCount);
	int testNum = 1 + entryCount;
	char ans;//, dump;
	
	CLEARSCR;
	printf("Add Test Report\n\n");
	testReport.testNumber = testNum;
	printf("Test Number: %d\n", testReport.testNumber);
	testReport.userProjectId = userProjectId;
    utilFlush();
	printf("Test Objective: ");
	gets(testReport.testObjective);
    utilFlush();
	printf("Test Description: ");
	gets(testReport.testDescription);
    utilFlush();
	printf("Test Conditions: ");
	gets(testReport.testConditions);
    utilFlush();
	printf("Expected Results: ");
	gets(testReport.expectedResults);
    
	utilFlush();
	printf("Save the details (Y/N)?");
	scanf("%c", &ans);
	if(ans == 'y' || ans == 'Y'){
		int trans = trtInsertDetails(conn, testReport);
		if(!trans){
			printf("\nError: Transaction Failed; Check database status...\n");
		}
		else{
			printf("\nReport Added!");
		}
        utilFlush();
		getch();
	}
	else{
	}
}

void imTrtMenu(MYSQL* conn, Project project, Phase phase){
	int choice;
	char dump;
	
	do{
		CLEARSCR;
		printf("Test Report Template\n");
        printf("* Please be reminded that you cannot get out of this phase without fixing all defects. *\n\n");
        
		printf("\t[1] Add Test Report\n");
		printf("\t[2] Add Actual Results\n");
		printf("\t[3] View Test Reports\n");
        printf("\t[4] Manage Defects\n\n");
		printf("\t[0] Go Back\n\n");
        utilFlush();
		printf("Choice: ");
		scanf("%d%c", &choice, &dump);
		
		switch(choice){
			case 1: imTrtGetInput(conn, project.userProjectId); break;
			case 2: trtInputActualResults(conn, project.userProjectId); break;
			case 3: imTrtDisplayAllReports(conn, project.userProjectId); break;
            case 4: imDrlViewDefectOptions(conn, project, phase); break;
			case 0: /*BACK to*/break;
			default: printf("Invalid Input - choices are 0,1,2,3, or 4 only...");break;
		}
	} while(choice != 0);
}

/**PROCESS IMPROVEMENT PLAN**/

void imPipDisplayPlan(MYSQL* conn, int piplanId){
	CLEARSCR;
	printf("Process Improvement Plan\n\n");
	printf("PIP Number  Problem Description\n");
	pipSelectDetails(conn, 1, piplanId);
	printf("\nProposal    Problem Description\nPIP Number\n");
	pipSelectDetails(conn, 2, piplanId);
	printf("\n\nNotes and Comments\n");
	pipSelectDetails(conn, 3, piplanId);
}

void imPipInputNotes(MYSQL* conn, ProcImpPlan procImpPlan){
	char ans;
	char dump;
    utilFlush();
	printf("Done adding problem and proposal?\n");
	printf("Do you wish to add notes and comments now?");
	scanf("%c%c", &ans, &dump);
	int trans;
	
	if(ans == 'y' || ans == 'Y'){
		imPipDisplayPlan(conn, procImpPlan.piplanId);
        utilFlush();
		printf("\n\nNotes and Comments:\n");
		gets(procImpPlan.notes);
        utilFlush();
		printf("\n\nSave the details (Y/N)? ");
		scanf("%c", &ans);
		
		if(ans == 'y' || ans == 'Y'){
			trans = pipUpdateDetails(conn, procImpPlan.notes,
                                     procImpPlan.piplanId);
			if(!trans){
				printf("\nError: Transaction Failed; Check database status...\n");
			}
			else{
				printf("Notes and Comments Added!");
			}
            utilFlush();
			getch();
		}
	}
}

void imPipInputProposal(MYSQL* conn, int piplanId){
	char ans;
	ProcImpProp procImpProp;
	procImpProp.piplanId = piplanId;
	imPipDisplayPlan(conn, piplanId);
	string300 query;
	strcpy(query, "SELECT COUNT(*) FROM pipproposal WHERE piplan_id = ");
	char projId[11];
	sprintf(projId, "%d", piplanId);
	strcat(query, projId);
	strcat(query, ";");
	procImpProp.proposalId = 1 + pipCountDetails(conn, query);
	printf("\n\nProposal ID: %d\n", procImpProp.proposalId);
    utilFlush();
	printf("Description\n");
	gets(procImpProp.description);
    utilFlush();
	printf("\n\nSave the details (Y/N)? ");
	scanf("%c", &ans);
	
	if(ans == 'y' || ans == 'Y'){
		int trans = pipInsertDetails(conn, 2, procImpProp.proposalId,
                                     procImpProp.piplanId, procImpProp.description);
		if(!trans){
			printf("\nError: Transaction Failed; Check database status...");
		}
		else{
			printf("Proposal Added!");
		}
        utilFlush();
		getch();
	}
}

void imPipInputProblem(MYSQL* conn, int piplanId){
	char ans;
	ProcImpProb procImpProb;
	procImpProb.piplanId = piplanId;
	imPipDisplayPlan(conn, piplanId);
	string300 query;
	strcpy(query, "SELECT COUNT(*) FROM pipproblem WHERE piplan_id=");
	char projId[11];
	sprintf(projId, "%d", piplanId);
	strcat(query, projId);
	strcat(query, ";");
	procImpProb.problemId = 1 + pipCountDetails(conn, query);
	printf("\n\nProblem ID: %d\n", procImpProb.problemId);
    utilFlush();
	printf("Description\n");
	gets(procImpProb.description);
    utilFlush();
	printf("\n\nSave details (Y/N)? ");
	scanf("%c", &ans);
	
	if(ans == 'y' || ans == 'Y'){
		int trans = pipInsertDetails(conn, 1, procImpProb.problemId,
                                     procImpProb.piplanId, procImpProb.description);
		if(!trans){
			printf("\nError: Transaction Failed; Check database status...\n");
		}
		else{
			printf("Problem Added!");
		}
		getch();
	}
}

void imPipMenu(MYSQL* conn, int userProjectId){
	int choice;
	char dump;
	ProcImpPlan procImpPlan;
	procImpPlan.userProjectId = userProjectId;
	string300 queryCount;
	strcpy(queryCount, "SELECT COUNT(*) FROM piplan WHERE user_project_id =");
	char projId[11];
	sprintf(projId, "%d", userProjectId);
	strcat(queryCount, projId);
	strcat(queryCount, ";");
	int idExist = pipCountDetails(conn, queryCount);
	if(idExist == 0){
		string300 queryCountPipId;
		strcpy(queryCountPipId, "SELECT COUNT(*) FROM piplan;");
		procImpPlan.piplanId = 1 + pipCountDetails(conn, queryCountPipId);
		string800 notes;
		strcpy(notes, "Place Notes and Comments here...");
		pipInsertDetails(conn, 3, procImpPlan.userProjectId,
                         procImpPlan.piplanId, notes);
	}
	else{
		procImpPlan.piplanId = idExist;
	}
	do{
		CLEARSCR;
		printf("Process Improvement Plan\n\n");
		printf("[1] Add Problem\n");
		printf("[2] Add Proposal \n");
		printf("[3] Add Notes \n");
		printf("[4] View Process Improvement Plan \n\n");
		printf("[0] Go Back \n\n");
        utilFlush();
		printf("Choice: ");
		scanf("%d%c", &choice, &dump);
		
		switch(choice){
			case 1: imPipInputProblem(conn, procImpPlan.piplanId); break;
			case 2: imPipInputProposal(conn, procImpPlan.piplanId); break;
			case 3: imPipInputNotes(conn, procImpPlan); break;
			case 4: imPipDisplayPlan(conn, procImpPlan.piplanId); getch(); break;
			case 0: /*BACK to*/break;
			default: printf("Invalid Input, choices are 1, 2, 3, 4, or 0 only...");
		}
	}while(choice != 0);
}

/**PROJECT PLAN SUMMARY**/
void imPpsDisplayProjectPlanSummary(MYSQL* conn, Project project)
{
    PlannedTime plannedTime;
    plannedTime = ppsGetPlannedTime(conn, project);
    
    PlannedLOC plannedLOC;
    plannedLOC = ppsGetPlannedLOC(conn, project);
    
    ActualLOC actualLOC;
    actualLOC = ppsGetActualLOC(conn, project);
    
    CLEARSCR;
    
    printf("PROJECT PLAN SUMMARY\n\n");
    
    printf("\nProgram Size (LOC)\tPlan\tActual\tTo Date\n");
    printf("========================================================\n");
    printf("Base\t\t\t%.2f\t%d\t100\n", plannedLOC.planBaseLOC, actualLOC.actualBaseLOC);
    printf("  Deleted\t\t%.2f\t%d\t100\n", plannedLOC.planDeletedLOC, actualLOC.actualDeletedLOC);
    printf("  Modified\t\t%.2f\t%d\t100\n", plannedLOC.planModifiedLOC, actualLOC.actualModifiedLOC);
    printf("  Added\t\t\t%.2f\t%d\t100\n", plannedLOC.planAddedLOC, actualLOC.actualAddedLOC);
    printf("  Reused\t\t%.2f\t%d\t100\n", plannedLOC.planReusedLOC, actualLOC.actualReusedLOC);
    printf("Total New and Changed\t%.2f\t%d\t100\n", plannedLOC.planTotalNewAndChanged, actualLOC.actualTotalNewAndChanged);
    printf("Total LOC\t\t%.2f\t%d\t100\n", ppsTotalPlannedLOC(plannedLOC), ppsTotalActualLOC(actualLOC));
    printf("Total New Reused\t0\t0\t100\n");
    
    printf("\nTime in Phase (min.)\tPlan\tActual\tTo Date\tTo Date%%\n");
    printf("========================================================\n");
    printf("Planning\t\t%d\t100\t100\t100\n", plannedTime.planningTime);
    printf("Design\t\t\t%d\t100\t100\t100\n", plannedTime.designTime);
    printf("Code\t\t\t%d\t100\t100\t100\n", plannedTime.codeTime);
    printf("Compile\t\t\t%d\t100\t100\t100\n", plannedTime.compileTime);
    printf("Test\t\t\t%d\t100\t100\t100\n", plannedTime.testTime);
    printf("Postmortem\t\t%d\t100\t100\t100\n", plannedTime.postMortemTime);
    printf("  Total\t\t\t100");
    
    printf("\n\nPress any key to continue.");
    utilFlush();
    getch();
}

/**PROJECT MANAGEMENT**/
void imPmSelectProjectForViewForms(MYSQL* conn, Project* projects, unsigned long numberOfProjects) {
    int projectCtr;
    char* choices[numberOfProjects + 1];
    
    for (projectCtr = 0; projectCtr < numberOfProjects; projectCtr++) {
        choices[projectCtr] = malloc(sizeof(char) * (strlen("PSP ") + 2));
        sprintf(choices[projectCtr], "PSP %d", projectCtr + 1);
    }
    
    char* backString = "Back";
    choices[numberOfProjects] = malloc(sizeof(char) * (strlen(backString) + 1));
    sprintf(choices[numberOfProjects], "%s", backString);
    
    CLEARSCR;
    unsigned int choice = imSelectFromChoices(choices, numberOfProjects + 1, "Select the project to view forms from");
    
    if (!choice == numberOfProjects + 1) {
        imViewForms(conn, projects[choice - 1]);
    }
}

void imPmViewProjectDetails(MYSQL* conn, MYSQL_RES *res){
    MYSQL_ROW row;
    int isDone = 1;
    int hasCurrent = 0;
    int isIdle = 0;
    int currentProjectDisplayNumber = 0;
    int pmInput;
    utilFlush();
    
    //project status contains the "done" column of the project table.
    const unsigned long numOfProjects = mysql_num_rows(res);
    const unsigned int numOfFields = mysql_num_fields(res);
    Project projects[numOfProjects];
    int x = 0;
    while((row = mysql_fetch_row(res)) != NULL){
        projects[x] = pmConvertRow(row, numOfFields);
        x++;
    }
    
    int projectCtr;
    
    do{
	  	isDone = 1;
	  	currentProjectDisplayNumber = 0;
	  	CLEARSCR;
	  	printf("Personal Software Process\n\n");
	  	for(projectCtr = 1; projectCtr <= numOfProjects; projectCtr++){
	  		Project currentProject = projects[projectCtr - 1];
            
	    	if(currentProject.done == 0 && isDone){
	      		isDone = 0;
	      		hasCurrent = 1;
	    	}
            else if(currentProject.done == 0 && hasCurrent){
	      		hasCurrent = 0;
	      		isIdle = 1;
	    	}
	    	
		    if(isDone){
                printf("PSP %d - done\n", projectCtr);
		    }
            else if(hasCurrent){
                currentProjectDisplayNumber = projectCtr;
                printf("PSP %d - current\n", projectCtr);
		    }
            else if(isIdle){
                printf("PSP %d - not yet started\n", projectCtr);
		    }
	  	}
        
        
		printf("\n\t[1] View Project Scripts\n");
        
        int hasProjectToContinue = 0;
        
		if (!currentProjectDisplayNumber) {
			printf("\nYou are currently done with everything!");
		}
		else {
            hasProjectToContinue = 1;
		  	printf("\t[2] Continue Project %d", currentProjectDisplayNumber);
		}
		printf("\n\n\t[0] Log Out");
		printf("\n\nchoice: ");
		scanf("%d", &pmInput);
		utilFlush();
        
		if(hasProjectToContinue && pmInput == 2) {
            Project currentProject = projects[currentProjectDisplayNumber - 1];
            Phase* projectCurrentPhase = NULL;
            if (trlHasCurrentPhase(conn, currentProject)) {
                //the user is working on a phase and it could be paused/stopped.
                projectCurrentPhase = trlGetCurrentPhase(conn, currentProject);
            }
            else {
                //not in any phase... this could also mean that the user is IN BETWEEN phases as well.
                projectCurrentPhase = trlGetUpcomingPhase(conn, currentProject);
            }
            
            imTrlRecordTime(conn, currentProject, *projectCurrentPhase);
		}
		else if(pmInput == 1){
		    imPmSelectProjectForViewForms(conn, projects, numOfProjects);
		}
        else if (pmInput == 0) { }
		else {
            if (hasProjectToContinue) {
                printf("Invalid Input, choices are 0, 1, or 2 only...");
            }
            else {
                printf("Invalid input, choices are 0 or 1 only...");
            }
		  	getch();
		}
	}while(pmInput != 0);
    
}

void imPmViewProjects(MYSQL *conn, string40 usmUserName){
    MYSQL_RES *res;
    string500 usmQuery;
    sprintf(usmQuery, "select * from project where user_id = (select user_id from user where user_name = '%s');", usmUserName);
    mysql_query(conn,usmQuery);
    res = mysql_store_result(conn);
    imPmViewProjectDetails(conn, res);
    mysql_free_result(res);
}


/**MAIN**/
int main() {
	return imDisplayMain();
}